Dumped on 2009-07-29
standard public schema
An account that may be used for authenticating to Canonical or other systems.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| creation_rationale | integer | NOT NULL | |
| status | integer | NOT NULL The status of the account. |
|
| date_status_set | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) When the status was last changed. |
|
| displayname | text | NOT NULL Name to display when rendering information about this account. |
|
| openid_identifier | text | UNIQUE NOT NULL DEFAULT
generate_openid_identifier() The key used to construct an OpenID identity URL for this account. |
|
| status_comment | text | The comment on the status of the account. |
|
| old_openid_identifier | text | The previous openid_identifier, used for transitions to the current openid_identifier. |
Tables referencing this one via Foreign Key Constraints:
account__old_openid_identifier__idx old_openid_identifierA password used to authenticate an Account.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.account.id | account | integer | UNIQUE NOT NULL |
| password | text | NOT NULL SSHA digest encrypted password. |
| F-Key | Name | Type | Description |
|---|---|---|---|
| procpid | integer | ||
| usename | name | ||
| age | interval | ||
| relname | name | ||
| mode | text | ||
| granted | boolean | ||
| current_query | text |
SELECT a.procpid
, a.usename
, (now
() - a.query_start
) AS age
, c.relname
, l.mode
, l.granted
, a.current_query
FROM (
(pg_locks l
JOIN pg_class c
ON (
(l.relation = c.oid)
)
)
LEFT JOIN pg_stat_activity a
ON (
(a.procpid = l.pid)
)
);
A project announcement. This is a single item of news or information that the project is communicating. Announcements can be attached to a Project, a Product or a Distribution.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| date_announced | timestamp without time zone | The date at which an announcement will become public, if it is active. If this is not set then the announcement will not become public until someone consciously publishes it (which sets this date). |
|
| public.person.id | registrant | integer | NOT NULL |
| public.product.id | product | integer | |
| public.distribution.id | distribution | integer | |
| public.project.id | project | integer | |
| title | text | NOT NULL | |
| summary | text | ||
| url | text | A web location for the announcement itself. |
|
| active | boolean | NOT NULL DEFAULT true Whether or not the announcement is public. This is TRUE by default, but can be set to FALSE if the project "retracts" the announcement. |
|
| date_updated | timestamp without time zone |
| Name | Constraint |
|---|---|
| has_target | CHECK ((((product IS NOT NULL) OR (project IS NOT NULL)) OR (distribution IS NOT NULL))) |
| valid_url | CHECK (valid_absolute_url(url)) |
Defines the answer contact for a given question target. The answer contact will be automatically notified about changes to any questions filed on the question target.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.product.id | product | integer | UNIQUE#2 The product that the answer contact supports. |
| public.distribution.id | distribution | integer | UNIQUE#1 The distribution that the answer contact supports. |
| public.sourcepackagename.id | sourcepackagename | integer | UNIQUE#1 The sourcepackagename that the answer contact supports. |
| public.person.id | person | integer | UNIQUE#1 UNIQUE#2 NOT NULL The person or team associated with the question target. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date the answer contact was submitted. |
| Name | Constraint |
|---|---|
| valid_target | CHECK ((((product IS NULL) <> (distribution IS NULL)) AND ((product IS NULL) OR (sourcepackagename IS NULL)))) |
A package archive. Commonly either a distribution's main_archive or a ppa's archive.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | owner | integer | NOT NULL Identifies the PPA owner when it has one. |
| description | text | Allow users to describe their PPAs content. |
|
| enabled | boolean | NOT NULL DEFAULT true Whether or not the PPA is enabled for accepting uploads. |
|
| authorized_size | integer | Size, in MiB, allowed for this PPA. |
|
| public.distribution.id | distribution | integer | NOT NULL The distribution that uses this archive. |
| purpose | integer | NOT NULL The purpose of this archive, e.g. COMMERCIAL. See the ArchivePurpose DBSchema item. |
|
| private | boolean | NOT NULL DEFAULT false Whether or not the archive is private. This affects the global visibility of the archive. |
|
| sources_cached | integer | Number of sources already cached for this archive. |
|
| binaries_cached | integer | Number of binaries already cached for this archive. |
|
| package_description_cache | text | Text blob containing all source and binary names and descriptions concatenated. Used to to build the tsearch indexes on this table. |
|
| fti | ts2.tsvector | ||
| buildd_secret | text | ||
| require_virtualized | boolean | NOT NULL DEFAULT true Whether this archive has binaries that should be built on a virtual machine, e.g. PPAs |
|
| name | text | NOT NULL DEFAULT 'default'::text The name of the archive. |
|
| publish | boolean | NOT NULL DEFAULT true Whether this archive should be published. |
|
| date_updated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) When were the rebuild statistics last updated? |
|
| total_count | integer | NOT NULL How many source packages are in the rebuild archive altogether? |
|
| pending_count | integer | NOT NULL How many packages still need building? |
|
| succeeded_count | integer | NOT NULL How many source packages were built sucessfully? |
|
| failed_count | integer | NOT NULL How many packages failed to build? |
|
| building_count | integer | NOT NULL How many packages are building at present? |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.gpgkey.id | signing_key | integer | The GpgKey used for signing this archive. |
| removed_binary_retention_days | integer | The number of days before superseded or deleted binary files are expired in the librarian, or zero for never. |
|
| num_old_versions_published | integer | The number of versions of a package to keep published before older versions are superseded. |
|
| displayname | text | NOT NULL User defined displayname for this archive. |
|
| relative_build_score | integer | NOT NULL A delta to the build score that is applied to all builds in this archive. |
| Name | Constraint |
|---|---|
| valid_buildd_secret | CHECK ((((private = true) AND (buildd_secret IS NOT NULL)) OR (private = false))) |
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
archive__owner__idx owner archive__signing_key__idx signing_key) WHERE (signing_key IS NOT NULL archive_fti ftiArchiveArch: A table that allows a user to specify which architectures an archive requires or supports.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.archive.id | archive | integer | UNIQUE#1 NOT NULL The archive for which an architecture is specified. |
| public.processorfamily.id | processorfamily | integer | UNIQUE#1 NOT NULL The architecture specified for the archive on hand. |
Authorisation tokens to use in .htaccess for published archives.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.archive.id | archive | integer | NOT NULL The archive to which this token refers. |
| public.person.id | person | integer | NOT NULL The person to which this token applies. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date and time this token was created. |
|
| date_deactivated | timestamp without time zone | The date and time this token was deactivated. |
|
| token | text | UNIQUE NOT NULL The token text for this authorisation. |
This table maps a given archive to all other archives it should depend on.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) Instant when the dependency was created. |
|
| public.archive.id | archive | integer | UNIQUE#1 NOT NULL The archive where the dependency should be applied. |
| public.archive.id | dependency | integer | UNIQUE#1 NOT NULL The archive to depend on. |
| integer | NOT NULL | ||
| public.component.id | component | integer |
| Name | Constraint |
|---|---|
| distinct_archives | CHECK ((archive <> dependency)) |
ArchivePermission: A record of who has permission to upload and approve uploads to an archive (and hence a distribution)
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date that this permission was created. |
|
| public.person.id | person | integer | NOT NULL The person or team to whom the permission is being granted. |
| permission | integer | NOT NULL The permission type being granted. |
|
| public.archive.id | archive | integer | NOT NULL The archive to which this permission applies. |
| public.component.id | component | integer | The component to which this upload permission applies. |
| public.sourcepackagename.id | sourcepackagename | integer | The source package name to which this permission applies. This can be used to provide package-level permissions to single users. |
| public.packageset.id | packageset | integer | The package set to which this permission applies. |
| explicit | boolean | NOT NULL DEFAULT false This flag is set for package sets containing high-profile packages that must not break and/or require specialist skills for proper handling e.g. the kernel. |
| Name | Constraint |
|---|---|
| one_target | CHECK ((null_count(ARRAY[packageset, component, sourcepackagename]) = 2)) |
An authorised person or team subscription to an archive.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.archive.id | archive | integer | NOT NULL The archive that the subscriber is authorised to see. |
| public.person.id | registrant | integer | NOT NULL The person who authorised this subscriber. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date and time this subscription was created. |
|
| public.person.id | subscriber | integer | NOT NULL The person or team that this subscription refers to. |
| date_expires | timestamp without time zone | The date and time this subscription will expire. If NULL, it does not expire. |
|
| status | integer | NOT NULL The status of the subscription, e.g. PENDING, ACTIVE, CANCELLING, CANCELLED. |
|
| description | text | An optional note for the archive owner to describe the subscription. |
|
| date_cancelled | timestamp without time zone | The date and time this subscription was revoked. |
|
| public.person.id | cancelled_by | integer | The person who revoked this subscription. |
AuthToken stores one time tokens used by the authentication service for validating email addresses and other tasks that require verifying an email address is valid such as password recovery and account merging. This table will be cleaned occasionally to remove expired tokens. Expiry time is not yet defined.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The timestamp that this request was made. |
|
| date_consumed | timestamp without time zone | The date and time when this token was consumed. It's NULL if it hasn't been consumed yet. |
|
| token_type | integer | NOT NULL The type of request, as per dbschema.TokenType. |
|
| token | text | UNIQUE NOT NULL The token (not the URL) emailed used to uniquely identify this request. This token will be used to generate a URL that when clicked on will continue a workflow. |
|
| public.account.id | requester | integer | The Account that made this request. This will be null for password recovery requests. |
| requester_email | text | The email address that was used to login when making this request. This provides an audit trail to help the end user confirm that this is a valid request. It is not a link to the EmailAddress table as this may be changed after the request is made. This field will be null for password recovery requests. |
|
| text | NOT NULL The email address that this request was sent to. |
||
| redirection_url | text |
| F-Key | Name | Type | Description |
|---|---|---|---|
| name | text |
SELECT binarypackagename.name FROM binarypackagename UNIONSELECT sourcepackagename.name FROM sourcepackagename;
BinaryPackageFile: A soyuz <-> librarian link table. This table represents the ownership in the librarian of a file which represents a binary package
| F-Key | Name | Type | Description |
|---|---|---|---|
| public.binarypackagerelease.id | binarypackagerelease | integer | NOT NULL The binary package which is represented by the file |
| public.libraryfilealias.id | libraryfile | integer | NOT NULL The file in the librarian which represents the package |
| filetype | integer | NOT NULL The "type" of the file. E.g. DEB, RPM |
|
| id | serial | PRIMARY KEY |
This view is used mostly by Lucille while performing publishing and unpublishing operations. It lists all the files associated with a binarypackage and collates all the textual representations needed for publishing components etc to allow rapid queries from SQLObject.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | text | ||
| distribution | integer | ||
| binarypackagepublishing | integer | ||
| componentname | text | ||
| libraryfilealiasfilename | text | ||
| sourcepackagename | text | ||
| libraryfilealias | integer | ||
| distroseriesname | text | ||
| architecturetag | text | ||
| publishingstatus | integer | ||
| integer | |||
| archive | integer |
SELECT (
(
(libraryfilealias.id)::text || '.'::text
) ||
(securebinarypackagepublishinghistory.id)::text
) AS id
, distroseries.distribution
, securebinarypackagepublishinghistory.id AS binarypackagepublishing
, component.name AS componentname
, libraryfilealias.filename AS libraryfilealiasfilename
, sourcepackagename.name AS sourcepackagename
, binarypackagefile.libraryfile AS libraryfilealias
, distroseries.name AS distroseriesname
, distroarchseries.architecturetag
, securebinarypackagepublishinghistory.status AS publishingstatus
, securebinarypackagepublishinghistory.pocket
, securebinarypackagepublishinghistory.archive
FROM (
(
(
(
(
(
(
(
(securebinarypackagepublishinghistory
JOIN binarypackagerelease
ON (
(securebinarypackagepublishinghistory.binarypackagerelease = binarypackagerelease.id)
)
)
JOIN build
ON (
(binarypackagerelease.build = build.id)
)
)
JOIN sourcepackagerelease
ON (
(build.sourcepackagerelease = sourcepackagerelease.id)
)
)
JOIN sourcepackagename
ON (
(sourcepackagerelease.sourcepackagename = sourcepackagename.id)
)
)
JOIN binarypackagefile
ON (
(binarypackagefile.binarypackagerelease = binarypackagerelease.id)
)
)
JOIN libraryfilealias
ON (
(binarypackagefile.libraryfile = libraryfilealias.id)
)
)
JOIN distroarchseries
ON (
(securebinarypackagepublishinghistory.distroarchseries = distroarchseries.id)
)
)
JOIN distroseries
ON (
(distroarchseries.distroseries = distroseries.id)
)
)
JOIN component
ON (
(securebinarypackagepublishinghistory.component = component.id)
)
)
WHERE (securebinarypackagepublishinghistory.dateremoved IS NULL);
BinaryPackageName: A soyuz binary package name.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL A lowercase name identifying one or more binarypackages |
| Name | Constraint |
|---|---|
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
View on SecureBinaryPackagePublishingHistory that restricts access to embargoed entries
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | integer | ||
| binarypackagerelease | integer | ||
| status | integer | ||
| component | integer | ||
| section | integer | ||
| priority | integer | ||
| distroarchseries | integer | ||
| integer | |||
| archive | integer | ||
| datecreated | timestamp without time zone | ||
| datepublished | timestamp without time zone | ||
| datesuperseded | timestamp without time zone | ||
| supersededby | integer | ||
| datemadepending | timestamp without time zone | ||
| scheduleddeletiondate | timestamp without time zone | ||
| dateremoved | timestamp without time zone | ||
| removed_by | integer | ||
| removal_comment | text | ||
| embargo | boolean | ||
| embargolifted | timestamp without time zone |
SELECT securebinarypackagepublishinghistory.id , securebinarypackagepublishinghistory.binarypackagerelease , securebinarypackagepublishinghistory.status , securebinarypackagepublishinghistory.component , securebinarypackagepublishinghistory.section , securebinarypackagepublishinghistory.priority , securebinarypackagepublishinghistory.distroarchseries , securebinarypackagepublishinghistory.pocket , securebinarypackagepublishinghistory.archive , securebinarypackagepublishinghistory.datecreated , securebinarypackagepublishinghistory.datepublished , securebinarypackagepublishinghistory.datesuperseded , securebinarypackagepublishinghistory.supersededby , securebinarypackagepublishinghistory.datemadepending , securebinarypackagepublishinghistory.scheduleddeletiondate , securebinarypackagepublishinghistory.dateremoved , securebinarypackagepublishinghistory.removed_by , securebinarypackagepublishinghistory.removal_comment , securebinarypackagepublishinghistory.embargo , securebinarypackagepublishinghistory.embargolifted FROM securebinarypackagepublishinghistory WHERE (securebinarypackagepublishinghistory.embargo = false);
BinaryPackageRelease: A soyuz binary package representation. This table stores the records for each binary package uploaded into the system. Each sourcepackagerelease may build various binarypackages on various architectures.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.binarypackagename.id | binarypackagename | integer | UNIQUE#1 UNIQUE#2 NOT NULL A reference to the name of the binary package |
| version | text | UNIQUE#1 NOT NULL The version of the binary package. E.g. "1.0-2" |
|
| summary | text | NOT NULL A summary of the binary package. Commonly used on listings of binary packages |
|
| description | text | NOT NULL A longer more detailed description of the binary package |
|
| public.build.id | build | integer | UNIQUE#1 UNIQUE#2 NOT NULL The build in which this binarypackage was produced |
| binpackageformat | integer | NOT NULL The binarypackage format. E.g. RPM, DEB etc |
|
| public.component.id | component | integer | NOT NULL The archive component that this binarypackage is in. E.g. main, universe etc |
| public.section.id | section | integer | NOT NULL The archive section that this binarypackage is in. E.g. devel, libdevel, editors |
| priority | integer | NOT NULL The priority that this package has. E.g. Base, Standard, Extra, Optional |
|
| shlibdeps | text | The shared library dependencies of this binary package |
|
| depends | text | The list of packages this binarypackage depends on |
|
| recommends | text | The list of packages this binarypackage recommends. Recommended packages often enhance the behaviour of a package. |
|
| suggests | text | The list of packages this binarypackage suggests. |
|
| conflicts | text | The list of packages this binarypackage conflicts with. |
|
| replaces | text | The list of packages this binarypackage replaces files in. Often this is used to provide an upgrade path between two binarypackages of different names |
|
| provides | text | The list of virtual packages (or real packages under some circumstances) which this binarypackage provides. |
|
| essential | boolean | NOT NULL Whether or not this binarypackage is essential to the smooth operation of a base system |
|
| installedsize | integer | What the installed size of the binarypackage is. This is represented as a number of kilobytes of storage. |
|
| architecturespecific | boolean | NOT NULL This field indicates whether or not a binarypackage is architecture-specific. If it is not specific to any given architecture then it can automatically be included in all the distroarchseries which pertain. |
|
| fti | ts2.tsvector | ||
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| pre_depends | text | The list of packages this binary requires to be installed beforehand in apt/dpkg format, as it is in control file "Pre-Depends:" field. |
|
| enhances | text | The list of packages pointed as "enhanced" after the installation of this package, as it is in control file "Enhances:" field. |
|
| breaks | text | The list of packages which will be broken by the installtion of this package, as it is in the control file "Breaks:" field. |
| Name | Constraint |
|---|---|
| valid_version | CHECK (valid_debian_version(version)) |
Tables referencing this one via Foreign Key Constraints:
binarypackagerelease_build_idx build binarypackagerelease_fti fti binarypackagerelease_version_idx version binarypackagerelease_version_sort debversion_sort_key(version)A set of bounties for work to be done by the open source community. These bounties will initially be offered only by Canonical, but later we will create the ability for people to offer the bounties themselves, using us as a clearing house.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL | |
| title | text | NOT NULL | |
| summary | text | NOT NULL | |
| description | text | NOT NULL | |
| usdvalue | numeric(10,2) | NOT NULL This is the ESTIMATED value in US Dollars of the bounty. We say "estimated" because the bounty might one day be offered in one of several currencies, or people might contribute different amounts in different currencies to each bounty. This field will reflect an estimate based on recent currency exchange rates of the value of this bounty in USD. |
|
| difficulty | integer | NOT NULL An estimate of the difficulty of the bounty, as a dbschema.BountyDifficulty. |
|
| public.person.id | reviewer | integer | NOT NULL The person who will review this bounty regularly for progress. The reviewer is the person who is responsible for establishing when the bounty is complete. |
| datecreated | timestamp without time zone | DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | owner | integer | NOT NULL The person who created the bounty. The owner can update the specification of the bounty, and appoints the reviewer. |
| deadline | timestamp without time zone | ||
| public.person.id | claimant | integer | |
| dateclaimed | timestamp without time zone | ||
| bountystatus | integer | NOT NULL DEFAULT 1 The current status of this bounty - an indicator of whether or not it is open, closed, or withdrawn. |
Tables referencing this one via Foreign Key Constraints:
bounty_usdvalue_idx usdvalue| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bounty.id | bounty | integer | UNIQUE#1 NOT NULL |
| public.message.id | message | integer | UNIQUE#1 NOT NULL |
This table records whether or not someone it interested in a bounty. Subscribers will show up on the page with the bounty details.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bounty.id | bounty | integer | UNIQUE#1 NOT NULL The bounty to which the person is subscribed. |
| public.person.id | person | integer | UNIQUE#1 NOT NULL The person being subscribed to this bounty. |
Bzr branch
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| title | text | ||
| summary | text | A single paragraph description of the branch |
|
| public.person.id | owner | integer | NOT NULL |
| public.product.id | product | integer | |
| public.person.id | author | integer | |
| name | text | NOT NULL | |
| home_page | text | This column is deprecated and to be removed soon. |
|
| url | text | UNIQUE | |
| whiteboard | text | Notes on the current status of the branch |
|
| lifecycle_status | integer | NOT NULL DEFAULT 1 Authors assesment of the branchs maturity |
|
| last_mirrored | timestamp without time zone | The time when the branch was last mirrored. |
|
| last_mirror_attempt | timestamp without time zone | ||
| mirror_failures | integer | NOT NULL | |
| mirror_status_message | text | The last message we got when mirroring this branch. |
|
| last_scanned | timestamp without time zone | The time when the branch was last scanned. |
|
| last_scanned_id | text | The revision ID of the branch when it was last scanned. |
|
| last_mirrored_id | text | The revision ID of the branch when it was last mirrored. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| revision_count | integer | NOT NULL The number of revisions in the associated bazaar branch revision_history. |
|
| next_mirror_time | timestamp without time zone | The time when we will next mirror this branch (NULL means never). This will be set automatically by pushing to a hosted branch, which, once mirrored, will be set back to NULL. |
|
| private | boolean | NOT NULL DEFAULT false If the branch is private, then only the owner and subscribers of the branch can see it. |
|
| branch_type | integer | NOT NULL Branches are currently one of HOSTED (1), MIRRORED (2), or IMPORTED (3). |
|
| public.person.id | reviewer | integer | The reviewer (person or) team are able to transition merge proposals targetted at the branch throught the CODE_APPROVED state. |
| public.branchmergerobot.id | merge_robot | integer | The robot that controls the automatic landing onto this branch. |
| merge_control_status | integer | NOT NULL DEFAULT 1 When there is no merge_robot set, the merge_control_status must be set to Manual. If a merge_robot is set, then the branch merge_control_status can be set to Automatic which means that the merge robot will start merging the branches. |
|
| date_last_modified | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) A branch is modified any time a user updates something using a view, a new revision for the branch is scanned, or the branch is linked to a bug, blueprint or merge proposal. |
|
| public.person.id | registrant | integer | NOT NULL The user that registered the branch. |
| branch_format | integer | The bzr branch format |
|
| repository_format | integer | The bzr repository format |
|
| metadir_format | integer | The bzr metadir format |
|
| public.branch.id | stacked_on | integer | The Launchpad branch that this branch is stacked on (if any). |
| public.distroseries.id | distroseries | integer | The distribution series that the branch belongs to. |
| public.sourcepackagename.id | sourcepackagename | integer | The source package this is a branch of. |
| owner_name | text | NOT NULL | |
| target_suffix | text | ||
| unique_name | text | UNIQUE | |
| size_on_disk | bigint | The size in bytes of this branch in the mirrored area. |
| Name | Constraint |
|---|---|
| branch_merge_control | CHECK (((merge_robot IS NULL) OR (merge_control_status = ANY (ARRAY[3, 4])))) |
| branch_type_url_consistent | CHECK (((((branch_type = 2) AND (url IS NOT NULL)) OR ((branch_type = ANY (ARRAY[1, 3])) AND (url IS NULL))) OR (branch_type = 4))) |
| branch_url_no_trailing_slash | CHECK ((url !~~ '%/'::text)) |
| branch_url_not_supermirror | CHECK ((url !~~ 'http://bazaar.launchpad.net/%'::text)) |
| one_container | CHECK ((((distroseries IS NULL) = (sourcepackagename IS NULL)) AND ((distroseries IS NULL) OR (product IS NULL)))) |
| valid_home_page | CHECK (valid_absolute_url(home_page)) |
| valid_name | CHECK (valid_branch_name(name)) |
| valid_url | CHECK (valid_absolute_url(url)) |
Tables referencing this one via Foreign Key Constraints:
branch__date_created__idx date_created branch__last_scanned__owner__idx last_scanned, owner) WHERE (last_scanned IS NOT NULL branch__next_mirror_time__idx next_mirror_time) WHERE (next_mirror_time IS NOT NULL branch__owner_name__idx owner_name branch__private__idx private branch__product__id__idx product, id branch__registrant__idx registrant branch__reviewer__idx reviewer branch__stacked_on__idx stacked_on) WHERE (stacked_on IS NOT NULL branch__target_suffix__idx target_suffix branch_author_idx author branch_owner_idx ownerContains references to jobs that are executed for a branch.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.job.id | job | integer | UNIQUE NOT NULL A reference to a row in the Job table that has all the common job details. |
| public.branch.id | branch | integer | The branch that this job is for. |
| job_type | integer | NOT NULL The type of job, like new revisions, or attribute change. |
|
| json_data | text | Data that is specific to the type of job, whether this be the revisions to send email out for, or the changes that were recorded for the branch. |
Branch merge proposals record the intent of landing (or merging) one branch on another.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | registrant | integer | NOT NULL The person that created the merge proposal. |
| public.branch.id | source_branch | integer | NOT NULL The branch where the work is being written. This branch contains the changes that the registrant wants to land. |
| public.branch.id | target_branch | integer | NOT NULL The branch where the user wants the changes from the source branch to be merged into. |
| public.branch.id | dependent_branch | integer | If the source branch was not branched off the target branch, then this is considered the dependent_branch. |
| whiteboard | text | Used to write other information about the branch, like test URLs. |
|
| date_merged | timestamp without time zone | This is the date that merge occurred. |
|
| merged_revno | integer | This is the revision number of the revision on the target branch that includes the merge from the source branch. |
|
| public.person.id | merge_reporter | integer | This is the user that marked the proposal as merged. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) When the registrant created the merge proposal. |
|
| commit_message | text | This is the commit message that is to be used when the branch is landed by a robot. |
|
| queue_position | integer | The position on the merge proposal in the overall landing queue. If the branch has a merge_robot set and the merge robot controls multiple branches then the queue position is unique over all the queued merge proposals for the landing robot. |
|
| queue_status | integer | NOT NULL DEFAULT 1 This is the current state of the merge proposal. |
|
| date_review_requested | timestamp without time zone | The date that the merge proposal enters the REVIEW_REQUESTED state. This is stored so that we can determine how long a branch has been waiting for code approval. |
|
| public.person.id | reviewer | integer | The individual who said that the code in this branch is OK to land. |
| date_reviewed | timestamp without time zone | When the reviewer said the code is OK to land. |
|
| reviewed_revision_id | text | The Bazaar revision ID that was approved to land. |
|
| public.person.id | queuer | integer | The individual who submitted the branch to the merge queue. This is usually the merge proposal registrant. |
| date_queued | timestamp without time zone | When the queuer submitted the branch to the merge queue. |
|
| queued_revision_id | text | The Bazaar revision ID that is queued to land. |
|
| public.person.id | merger | integer | The merger is the person who merged the branch. |
| merged_revision_id | text | The Bazaar revision ID that was actually merged. If the owner of the source branch is a trusted person, this may be different than the revision_id that was actually queued or reviewed. |
|
| date_merge_started | timestamp without time zone | If the merge is performed by a bot the time the merge was started is recorded otherwise it is NULL. |
|
| date_merge_finished | timestamp without time zone | If the merge is performed by a bot the time the merge was finished is recorded otherwise it is NULL. |
|
| public.libraryfilealias.id | merge_log_file | integer | If the merge is performed by a bot the log file is accessible from the librarian. |
| public.branchmergeproposal.id | superseded_by | integer | The proposal to merge has been superceded by this one. |
| root_message_id | text | The root message of this BranchMergeProposal's mail thread. |
|
| public.staticdiff.id | review_diff | integer | The diff to use for review purposes. |
| public.previewdiff.id | merge_diff | integer | The diff showing the predicted result of a merge. |
| Name | Constraint |
|---|---|
| different_branches | CHECK ((((source_branch <> target_branch) AND (dependent_branch <> source_branch)) AND (dependent_branch <> target_branch))) |
| positive_revno | CHECK (((merged_revno IS NULL) OR (merged_revno > 0))) |
Tables referencing this one via Foreign Key Constraints:
branchmergeproposal__dependent_branch__idx dependent_branch branchmergeproposal__merge_diff__idx merge_diff branchmergeproposal__merge_reporter__idx merge_reporter) WHERE (merge_reporter IS NOT NULL branchmergeproposal__merger__idx merger branchmergeproposal__queuer__idx queuer branchmergeproposal__review_diff__idx review_diff branchmergeproposal__reviewer__idx reviewer branchmergeproposal__source_branch__idx source_branch branchmergeproposal__superseded_by__idx superseded_by) WHERE (superseded_by IS NOT NULL branchmergeproposal__target_branch__idx target_branchContains references to jobs that are executed for a branch merge proposal.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.job.id | job | integer | UNIQUE NOT NULL A reference to a row in the Job table that has all the common job details. |
| public.branchmergeproposal.id | branch_merge_proposal | integer | NOT NULL The branch merge proposal that this job is for. |
| job_type | integer | NOT NULL The type of job, like new proposal, review comment, or new review requested. |
|
| json_data | text | Data that is specific to the type of job, normally references to code review messages and or votes. |
In order to have a single merge robot be able to control landings on multiple branches, we need some robot entity.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | registrant | integer | NOT NULL The person that created the merge robot. |
| public.person.id | owner | integer | NOT NULL The person or team that is able to update the robot and manage the landing queue. |
| name | text | UNIQUE NOT NULL The name of the robot. This is unique for the owner. |
|
| whiteboard | text | Any interesting comments about the robot itself. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) When this robot was created. |
Tables referencing this one via Foreign Key Constraints:
branchmergerobot__owner__idx owner branchmergerobot__registrant__idx registrant| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | UNIQUE#3 PRIMARY KEY | |
| sequence | integer | UNIQUE#4 | |
| public.branch.id | branch | integer | UNIQUE#1 UNIQUE#2 UNIQUE#3 UNIQUE#4 NOT NULL |
| public.revision.id | revision | integer | UNIQUE#1 UNIQUE#2 NOT NULL |
An association between a person or team and a bazaar branch.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | UNIQUE#1 NOT NULL The person or team associated with the branch. |
| public.branch.id | branch | integer | UNIQUE#1 NOT NULL The branch associated with the person or team. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| notification_level | integer | NOT NULL DEFAULT 1 The level of email the person wants to receive from branch updates. |
|
| max_diff_lines | integer | If the generated diff for a revision is larger than this number, then the diff is not sent in the notification email. |
|
| review_level | integer | NOT NULL The level of email the person wants to receive from review activity |
Defines the policy for the initial visibility of branches.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.project.id | project | integer | Even though projects don't directly have branches themselves, if a product of the project does not specify its own branch visibility policies, those of the project are used. |
| public.product.id | product | integer | The product that the visibility policies apply to. |
| public.person.id | team | integer | Refers to the team that the policy applies to. NULL is used to indicate ALL people, as there is no team defined for *everybody*. |
| policy | integer | NOT NULL DEFAULT 1 An enumerated type, one of PUBLIC or PRIVATE. PUBLIC is the default value. |
| Name | Constraint |
|---|---|
| only_one_target | CHECK (((project IS NULL) <> (product IS NULL))) |
A hack to allow the sorting of queries to Branch by human-meaningful keys in the face of limitations in SQLObject. Will go away when we start using Storm. This view has all the columns of Branch with three extra names joined on to it.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | integer | ||
| title | text | ||
| summary | text | ||
| owner | integer | ||
| product | integer | ||
| author | integer | ||
| name | text | ||
| home_page | text | ||
| url | text | ||
| whiteboard | text | ||
| lifecycle_status | integer | ||
| last_mirrored | timestamp without time zone | ||
| last_mirror_attempt | timestamp without time zone | ||
| mirror_failures | integer | ||
| mirror_status_message | text | ||
| last_scanned | timestamp without time zone | ||
| last_scanned_id | text | ||
| last_mirrored_id | text | ||
| date_created | timestamp without time zone | ||
| revision_count | integer | ||
| next_mirror_time | timestamp without time zone | ||
| private | boolean | ||
| branch_type | integer | ||
| reviewer | integer | ||
| merge_robot | integer | ||
| merge_control_status | integer | ||
| date_last_modified | timestamp without time zone | ||
| registrant | integer | ||
| branch_format | integer | ||
| repository_format | integer | ||
| metadir_format | integer | ||
| stacked_on | integer | ||
| product_name | text | Branch.product.name |
|
| author_name | text | Branch.author.displayname |
|
| owner_name | text | Branch.owner.displayname |
SELECT branch.id
, branch.title
, branch.summary
, branch.owner
, branch.product
, branch.author
, branch.name
, branch.home_page
, branch.url
, branch.whiteboard
, branch.lifecycle_status
, branch.last_mirrored
, branch.last_mirror_attempt
, branch.mirror_failures
, branch.mirror_status_message
, branch.last_scanned
, branch.last_scanned_id
, branch.last_mirrored_id
, branch.date_created
, branch.revision_count
, branch.next_mirror_time
, branch.private
, branch.branch_type
, branch.reviewer
, branch.merge_robot
, branch.merge_control_status
, branch.date_last_modified
, branch.registrant
, branch.branch_format
, branch.repository_format
, branch.metadir_format
, branch.stacked_on
, product.name AS product_name
, author.displayname AS author_name
, owner.displayname AS owner_name
FROM (
(
(branch
JOIN person owner
ON (
(branch.owner = owner.id)
)
)
LEFT JOIN product
ON (
(branch.product = product.id)
)
)
LEFT JOIN person author
ON (
(branch.author = author.id)
)
);
A software bug that requires fixing. This particular bug may be linked to one or more products or source packages to identify the location(s) that this bug is found.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| name | text | UNIQUE A lowercase name uniquely identifying the bug |
|
| title | text | NOT NULL | |
| description | text | NOT NULL A detailed description of the bug. Initially this will be set to the contents of the initial email or bug filing comment, but later it can be edited to give a more accurate description of the bug itself rather than the symptoms observed by the reporter. |
|
| public.person.id | owner | integer | NOT NULL |
| public.bug.id | duplicateof | integer | |
| fti | ts2.tsvector | ||
| private | boolean | NOT NULL DEFAULT false Is this bug private? If so, only explicit subscribers will be able to see it |
|
| security_related | boolean | NOT NULL DEFAULT false Is this bug a security issue? |
|
| date_last_updated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| date_made_private | timestamp without time zone | ||
| public.person.id | who_made_private | integer | |
| date_last_message | timestamp without time zone | When the last BugMessage was attached to this Bug. Maintained by a trigger on the BugMessage table. |
|
| number_of_duplicates | integer | NOT NULL The number of bugs marked as duplicates of this bug, populated by a trigger after setting the duplicateof of bugs. |
|
| message_count | integer | NOT NULL The number of messages (currently just comments) on this bugbug, maintained by the set_bug_message_count_t trigger. |
|
| users_affected_count | integer | The number of users affected by this bug, maintained by the set_bug_users_affected_count_t trigger. |
|
| users_unaffected_count | integer | ||
| hotness | integer | NOT NULL The relevance of this bug. This value is computed periodically using bug_affects_person and other bug values. |
| Name | Constraint |
|---|---|
| notduplicateofself | CHECK ((NOT (id = duplicateof))) |
| sane_description | CHECK (((ltrim(description) <> ''::text) AND (char_length(description) <= 50000))) |
| valid_bug_name | CHECK (valid_bug_name(name)) |
Tables referencing this one via Foreign Key Constraints:
bug__date_last_message__idx date_last_message bug__date_last_updated__idx date_last_updated bug__datecreated__idx datecreated bug__hotness__idx hotness bug__users_affected_count__idx users_affected_count bug__users_unaffected_count__idx users_unaffected_count bug__who_made_private__idx who_made_private) WHERE (who_made_private IS NOT NULL bug_duplicateof_idx duplicateof bug_fti fti bug_owner_idx owner| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bug.id | bug | integer | NOT NULL |
| datechanged | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| person | integer | NOT NULL | |
| whatchanged | text | NOT NULL | |
| oldvalue | text | ||
| newvalue | text | ||
| message | text |
This table maintains a mapping between bugs and users indicating that they are affected by that bug. The value is calculated and cached in the Bug.users_affected_count column.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL The bug affecting this person. |
| public.person.id | person | integer | UNIQUE#1 NOT NULL The person affected by this bug. |
| affected | boolean | NOT NULL DEFAULT true |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.message.id | message | integer | NOT NULL |
| name | text | ||
| title | text | ||
| public.libraryfilealias.id | libraryfile | integer | NOT NULL |
| public.bug.id | bug | integer | NOT NULL |
| type | integer | NOT NULL |
| Name | Constraint |
|---|---|
| valid_name | CHECK (valid_name(name)) |
A branch related to a bug, most likely a branch for fixing the bug.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL The bug associated with this branch. |
| public.branch.id | branch | integer | UNIQUE#1 NOT NULL The branch associated to the bug. |
| public.revision.id | revision_hint | integer | An optional revision at which this branch became interesting to this bug, and/or may contain a fix for the bug. |
| whiteboard | text | Additional information about the status of the bugfix in this branch. |
|
| public.person.id | registrant | integer | NOT NULL The person who linked the bug to the branch. |
A table that records the link between a given malone bug number, and a CVE entry.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL |
| public.cve.id | cve | integer | UNIQUE#1 NOT NULL |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
This table maps a message to a bug. In other words, it shows that a particular message is associated with a particular bug.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL |
| public.message.id | message | integer | UNIQUE#1 NOT NULL |
| public.bugwatch.id | bugwatch | integer | UNIQUE#2 The external bug this bug comment was imported from. |
| remote_comment_id | text | UNIQUE#2 The id this bug comment has in the external bug tracker, if it is an imported comment. If it is NULL while having a bugwatch set, this comment was added in Launchpad and needs to be pushed to the external bug tracker. |
|
| visible | boolean | NOT NULL DEFAULT true If false, the bug comment is hidden and should not be shown in any UI. |
| Name | Constraint |
|---|---|
| imported_comment | CHECK (((remote_comment_id IS NULL) OR (bugwatch IS NOT NULL))) |
A bug nominated for fixing in a distroseries or productseries
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bug.id | bug | integer | NOT NULL The bug being nominated. |
| public.distroseries.id | distroseries | integer | The distroseries for which the bug is nominated. |
| public.productseries.id | productseries | integer | The productseries for which the bug is nominated. |
| status | integer | NOT NULL The status of the nomination. |
|
| date_created | timestamp without time zone | DEFAULT timezone('UTC'::text, now()) The date the nomination was submitted. |
|
| date_decided | timestamp without time zone | The date the nomination was approved or declined. |
|
| public.person.id | owner | integer | NOT NULL The person that submitted the nomination |
| public.person.id | decider | integer | The person who approved or declined the nomination |
| Name | Constraint |
|---|---|
| distroseries_or_productseries | CHECK (((distroseries IS NULL) <> (productseries IS NULL))) |
The text representation of changes to a bug, which are used to send email notifications to bug changes.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL The bug that was changed. |
| public.message.id | message | integer | UNIQUE#1 NOT NULL The message the contains the textual representation of the change. |
| is_comment | boolean | NOT NULL Is the change a comment addition. |
|
| date_emailed | timestamp without time zone | When this notification was emailed to the bug subscribers. |
Tables referencing this one via Foreign Key Constraints:
bugnotification__date_emailed__idx date_emailedAttachments to be attached to a bug notification.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.message.id | message | integer | NOT NULL A message to be attached to the sent bug notification. It will be attached as a mime/multipart part, with a content type of message/rfc822. |
| public.bugnotification.id | bug_notification | integer | NOT NULL The bug notification, to which things should be attached to. |
The recipient for a bug notification.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bugnotification.id | bug_notification | integer | UNIQUE#1 NOT NULL The notification this recipient should get. |
| public.person.id | person | integer | UNIQUE#1 NOT NULL The person who should receive this notification. |
| reason_header | text | NOT NULL The reason this person is receiving this notification (the value for the X-Launchpad-Message-Rationale header). |
|
| reason_body | text | NOT NULL A line of text describing the reason this person is receiving this notification (to be included in the email message). |
A BugPackageInfestation records the impact that a bug is known to have on a specific sourcepackagerelease. This allows us to track the versions of a package that are known to be affected or unaffected by a bug.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL The Bug that infests this source package release. |
| public.sourcepackagerelease.id | sourcepackagerelease | integer | UNIQUE#1 NOT NULL The package (software) release that is infested with the bug. This points at the specific source package release version, such as "apache 2.0.48-1". |
| explicit | boolean | NOT NULL This field records whether or not the infestation was documented by a user of the system, or inferred from some other source such as the fact that it is documented to affect prior and subsequent releases of the package. |
|
| infestationstatus | integer | NOT NULL The nature of the bug infestation for this source package release. Values are documented in dbschema.BugInfestationStatus, and include AFFECTED, UNAFFECTED, FIXED and VICTIMISED. See the dbschema.py file for details. |
|
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | creator | integer | NOT NULL The person who recorded this infestation. Typically, this is the user who reports the specific problem on that specific package release. |
| dateverified | timestamp without time zone | The timestamp when the problem was verified on that specific release. This a small step towards a complete workflow for defect verification and management on specific releases. |
|
| public.person.id | verifiedby | integer | The person who verified that this infestation affects this specific package. |
| lastmodified | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The timestamp when this infestation report was last modified in any way. For example, when the infestation was adjusted, or it was verified, or otherwise modified. |
|
| public.person.id | lastmodifiedby | integer | NOT NULL The person who touched this infestation report last, in any way. |
A BugProductInfestation records the impact that a bug is known to have on a specific productrelease. This allows us to track the versions of a product that are known to be affected or unaffected by a bug.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL The Bug that infests this product release. |
| public.productrelease.id | productrelease | integer | UNIQUE#1 NOT NULL The product (software) release that is infested with the bug. This points at the specific release version, such as "apache 2.0.48". |
| explicit | boolean | NOT NULL This field records whether or not the infestation was documented by a user of the system, or inferred from some other source such as the fact that it is documented to affect prior and subsequent releases of the product. |
|
| infestationstatus | integer | NOT NULL The nature of the bug infestation for this product release. Values are documented in dbschema.BugInfestationStatus, and include AFFECTED, UNAFFECTED, FIXED and VICTIMISED. See the dbschema.py file for details. |
|
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | creator | integer | NOT NULL The person who recorded this infestation. Typically, this is the user who reports the specific problem on that specific product release. |
| dateverified | timestamp without time zone | The timestamp when the problem was verified on that specific release. This a small step towards a complete workflow for defect verification and management on specific releases. |
|
| public.person.id | verifiedby | integer | The person who verified that this infestation affects this specific product release. |
| lastmodified | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The timestamp when this infestation report was last modified in any way. For example, when the infestation was adjusted, or it was verified, or otherwise modified. |
|
| public.person.id | lastmodifiedby | integer | NOT NULL The person who touched this infestation report last, in any way. |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | NOT NULL |
| public.bug.id | bug | integer | NOT NULL |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.person.id | subscribed_by | integer | NOT NULL |
Attaches simple text tags to a bug.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| bug | integer | UNIQUE#1 NOT NULL The bug the tags is attached to. |
|
| tag | text | UNIQUE#1 NOT NULL The text representation of the tag. |
| Name | Constraint |
|---|---|
| valid_tag | CHECK (valid_name(tag)) |
Links a given Bug to a particular (sourcepackagename, distro) or product.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bug.id | bug | integer | NOT NULL The bug that is assigned to this (sourcepackagename, distro) or product. |
| public.product.id public.milestone.product#2 | product | integer | The product in which this bug shows up. |
| public.distribution.id public.milestone.distribution#1 | distribution | integer | The distro of the named sourcepackage. |
| public.distroseries.id | distroseries | integer | |
| public.sourcepackagename.id | sourcepackagename | integer | The name of the sourcepackage in which this bug shows up. |
| public.binarypackagename.id | binarypackagename | integer | The name of the binary package built from the source package. This column may only contain a value if this bug task is linked to a sourcepackage (not a product) |
| status | integer | NOT NULL The general health of the bug, e.g. Accepted, Rejected, etc. |
|
| priority | integer | Obsolete. |
|
| importance | integer | NOT NULL DEFAULT 5 The importance of fixing the bug. |
|
| public.person.id | assignee | integer | The person who has been assigned to fix this bug in this product or (sourcepackagename, distro) |
| date_assigned | timestamp without time zone | The date on which the bug in this (sourcepackagename, distro) or product was assigned to someone to fix |
|
| datecreated | timestamp without time zone | DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6)
with time zone) A timestamp for the creation of this bug assignment. Note that this is not the date the bug was created (though it might be), it's the date the bug was assigned to this product, which could have come later. |
|
| public.person.id | owner | integer | NOT NULL |
| public.milestone.id#1 public.milestone.id#2 | milestone | integer | A way to mark a bug for grouping purposes, e.g. to say it needs to be fixed by version 1.2 |
| public.bugwatch.id | bugwatch | integer | This column allows us to link a bug task to a bug watch. In other words, we are connecting the state of the task to the state of the bug in a different bug tracking system. To the best of our ability we'll try and keep the bug task syncronised with the state of the remote bug watch. |
| statusexplanation | text | A place to store bug task specific information as free text |
|
| fti | ts2.tsvector | ||
| targetnamecache | text | A cached value of the target name of this bugtask, to make it easier to sort and search on the target name. |
|
| date_confirmed | timestamp without time zone | The date when this bug transitioned from an unconfirmed status to a confirmed one. If the state regresses to a one that logically occurs before Confirmed, e.g., Unconfirmed, this date is cleared. |
|
| date_inprogress | timestamp without time zone | The date on which this bug transitioned from not being in progress to a state >= In Progress. If the status moves back to a pre-In Progress state, this date is cleared |
|
| date_closed | timestamp without time zone | The date when this bug transitioned to a resolved state, e.g., Rejected, Fix Released, etc. If the state changes back to a pre-closed state, this date is cleared |
|
| public.productseries.id | productseries | integer | The product series to which the bug is targeted |
| date_incomplete | timestamp without time zone | ||
| date_left_new | timestamp without time zone | The date when this bug first transitioned out of the NEW status. |
|
| date_triaged | timestamp without time zone | The date when this bug transitioned to a status >= TRIAGED. |
|
| date_fix_committed | timestamp without time zone | The date when this bug transitioned to a status >= FIXCOMMITTED. |
|
| date_fix_released | timestamp without time zone | The date when this bug transitioned to a FIXRELEASED status. |
|
| date_left_closed | timestamp without time zone | The date when this bug last transitioned out of a CLOSED status. |
|
| hotness_rank | integer | NOT NULL The hotness bin in which this bugtask appears, as a value from the BugTaskHotnessRank enumeration. |
|
| date_milestone_set | timestamp without time zone | The date when this bug was targed to the milestone that is currently set. |
| Name | Constraint |
|---|---|
| bugtask_assignment_checks | CHECK (CASE WHEN (product IS NOT NULL) THEN ((((productseries IS NULL) AND (distribution IS NULL)) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (productseries IS NOT NULL) THEN (((distribution IS NULL) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (distribution IS NOT NULL) THEN (distroseries IS NULL) WHEN (distroseries IS NOT NULL) THEN true ELSE false END) |
A bug tracker in some other project. Malone allows us to link Malone bugs with bugs recorded in other bug tracking systems, and to keep the status of the relevant bug task in sync with the status in that upstream bug tracker. So, for example, you might note that Malone bug #43224 is the same as a bug in the Apache bugzilla, number 534536. Then when the upstream guys mark that bug fixed in their bugzilla, Malone know that the bug is fixed upstream.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| bugtrackertype | integer | NOT NULL The type of bug tracker, a pointer to the table of bug tracker types. Currently we know about debbugs and bugzilla bugtrackers, and plan to support roundup and sourceforge as well. |
|
| name | text | NOT NULL The unique name of this bugtracker, allowing us to refer to it directly. |
|
| title | text | NOT NULL A title for the bug tracker, used in listings of all the bug trackers and also displayed at the top of the descriptive page for the bug tracker. |
|
| summary | text | A brief summary of this bug tracker, which might for example list any interesting policies regarding the use of the bug tracker. The summary is displayed in bold at the top of the bug tracker page. |
|
| baseurl | text | NOT NULL The base URL for this bug tracker. Using our knowledge of the bugtrackertype, and the details in the BugWatch table we are then able to calculate relative URLs for relevant pages in the bug tracker based on this baseurl. |
|
| public.person.id | owner | integer | NOT NULL The person who created this bugtracker entry and who thus has permission to modify it. Ideally we would like this to be the person who coordinates the running of the actual bug tracker upstream. |
| contactdetails | text | The contact details of the people responsible for that bug tracker. This allows us to coordinate the syncing of bugs to and from that bug tracker with the responsible people on the other side. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| version | text | The version of the bug tracker software being used. |
|
| block_comment_pushing | boolean | NOT NULL DEFAULT false Whether to block pushing comments to the bug tracker. Having a value of false means that we will push the comments if the bug tracker supports it. |
|
| has_lp_plugin | boolean | Whether we have confirmed that the Launchpad plugin was installed on the bug tracker, the last time checkwatches was run. |
|
| active | boolean | NOT NULL DEFAULT true |
| Name | Constraint |
|---|---|
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
bugtracker_owner_idx ownerA bugtracker alias is a URL that also refers to the same bugtracker as the master bugtracker. For example, a bugtracker might be accessible as both http://www.bugsrus.com/ and http://bugsrus.com/. A bugtracker can have many aliases, and all of them are checked to prevents users registering duplicate bugtrackers inadvertently.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bugtracker.id | bugtracker | integer | NOT NULL The master bugtracker that this alias refers to. |
| base_url | text | UNIQUE NOT NULL Another base URL for this bug tracker. See BugTracker.baseurl. |
A mapping from a user in an external bug tracker to a Person record in Launchpad. This is used when we can't get an e-mail address from the bug tracker.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) When was this mapping added. |
|
| public.bugtracker.id | bugtracker | integer | UNIQUE#1 NOT NULL The external bug tracker in which this user has an account. |
| public.person.id | person | integer | NOT NULL The Person record in Launchpad this user corresponds to. |
| name | text | UNIQUE#1 NOT NULL The (within the bug tracker) unique username in the external bug tracker. |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | UNIQUE#1 PRIMARY KEY | |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL |
| public.bugtracker.id | bugtracker | integer | NOT NULL |
| remotebug | text | NOT NULL | |
| remotestatus | text | ||
| lastchanged | timestamp without time zone | DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| lastchecked | timestamp without time zone | DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | owner | integer | NOT NULL |
| last_error_type | integer | The type of error which last prevented this entry from being updated. Legal values are defined by the BugWatchErrorType enumeration. |
|
| remote_importance | text | The importance of the bug as returned by the remote server. This will be converted into a Launchpad BugTaskImportance value. |
|
| remote_lp_bug_id | integer | The bug in Launchpad that the remote bug is pointing at. This can be different than the BugWatch.bug column, since the same remote bug can be linked from multiple bugs in Launchpad, but the remote bug can only link to a single bug in Launchpad. The main use case for this column is to avoid having to query the remote bug tracker for this information, in order to decide whether we need to give this information to the remote bug tracker. |
Tables referencing this one via Foreign Key Constraints:
bugwatch_bug_idx bug bugwatch_bugtracker_idx bugtracker bugwatch_datecreated_idx datecreated bugwatch_owner_idx ownerBuild: This table stores the build procedure information of a sourcepackagerelease and its results (binarypackagereleases) for a given distroarchseries.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datecreated | timestamp without time zone | UNIQUE NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) When the build record was created. |
|
| public.processor.id | processor | integer | NOT NULL Points to the Distroarchrelease available processor target for this build. |
| public.distroarchseries.id | distroarchseries | integer | UNIQUE#1 NOT NULL Points the target Distroarchrelease for this build. |
| buildstate | integer | NOT NULL Stores the current build procedure state. |
|
| datebuilt | timestamp without time zone | When the build record was processed. |
|
| buildduration | interval | How long this build took to be processed. |
|
| public.libraryfilealias.id | buildlog | integer | Points to the buildlog file stored in librarian. |
| public.builder.id | builder | integer | Points to the builder which has once processed it. |
| public.sourcepackagerelease.id | sourcepackagerelease | integer | UNIQUE#1 NOT NULL Sourcepackagerelease which originated this build. |
| integer | NOT NULL Stores the target pocket identifier for this build. |
||
| dependencies | text | Contains a debian-like dependency line specifying the current missing-dependencies for this package. |
|
| public.archive.id | archive | integer | UNIQUE#1 NOT NULL Targeted archive for this build. |
| estimated_build_duration | interval | How long does the previous attempt to build this source took in this architecture. |
|
| build_warnings | text | Warnings and diagnosis messages provided by the builder while building this job. |
|
| date_first_dispatched | timestamp without time zone | The instant the build was dispatched the first time. This value will not get overridden if the build is retried. |
|
| public.libraryfilealias.id | upload_log | integer | Reference to a LibraryFileAlias containing the upload log messages generated while processing the binaries resulted from this build. |
Tables referencing this one via Foreign Key Constraints:
build__archive__idx archive build__distroarchseries__buildstate__idx distroarchseries, buildstate build__distroarchseries__datebuilt__idx distroarchseries, datebuilt build__upload_log__idx upload_log) WHERE (upload_log IS NOT NULL build_builder_and_buildstate_idx builder, buildstate build_buildlog_idx buildlog) WHERE (buildlog IS NOT NULL build_buildstate_idx buildstate build_datebuilt_idx datebuilt build_datecreated_idx datecreated build_sourcepackagerelease_idx sourcepackagereleaseBuilder: This table stores the build-slave registry and status information as: name, url, trusted, builderok, builderaction, failnotes.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.processor.id | processor | integer | NOT NULL |
| name | text | NOT NULL | |
| title | text | NOT NULL | |
| description | text | NOT NULL | |
| public.person.id | owner | integer | NOT NULL |
| speedindex | integer | ||
| builderok | boolean | NOT NULL Should a builder fail for any reason, from out-of-disk-space to not responding to the buildd master, the builderok flag is set to false and the failnotes column is filled with a reason. |
|
| failnotes | text | This column gets filled out with a textual description of how/why a builder has failed. If the builderok column is true then the value in this column is irrelevant and should be treated as NULL or empty. |
|
| virtualized | boolean | NOT NULL DEFAULT true Whether or not the builder is a virtual Xen builder. Packages coming via ubuntu workflow are trusted to build on non-Xen and do not need facist behaviour to be built. Other packages like ppa/grumpy incoming packages can contain malicious code, so are unstrusted and build in a Xen virtual machine. |
|
| url | text | UNIQUE NOT NULL The url to the build slave. There may be more than one build slave on a given host so this url includes the port number to use. The default port number for a build slave is 8221 |
|
| manual | boolean | DEFAULT false Whether or not builder was manual mode, i.e., collect any result from the it, but do not dispach anything to it automatically. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| vm_host | text | The virtual machine host associated to this builder. It should be empty for "native" builders (old fashion or architectures not yet supported by XEN). |
|
| active | boolean | NOT NULL DEFAULT true Whether to present or not the builder in the public list of builders avaialble. It is used to hide transient or defunct builders while they get fixed. |
| Name | Constraint |
|---|---|
| valid_absolute_url | CHECK (valid_absolute_url(url)) |
Tables referencing this one via Foreign Key Constraints:
BuildQueue: The queue of builds in progress/scheduled to run. This table is the core of the build daemon master. It lists all builds in progress or scheduled to start.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.build.id | build | integer | NOT NULL The build for which this queue item exists. This is how the buildd master will find all the files it needs to perform the build |
| public.builder.id | builder | integer | The builder assigned to this build. Some builds will have a builder assigned to queue them up; some will be building on the specified builder already; others will not have a builder yet (NULL) and will be waiting to be assigned into a builder's queue |
| logtail | text | The tail end of the log of the current build. This is updated regularly as the buildd master polls the buildd slaves. Once the build is complete; the full log will be lodged with the librarian and linked into the build table. |
|
| created | timestamp without time zone | NOT NULL The timestamp of the creation of this row. This is used by the buildd master scheduling algorithm to decide how soon to schedule a build to run on a given builder. |
|
| buildstart | timestamp without time zone | The timestamp of the start of the build run on the given builder. If this is NULL then the build is not running yet. |
|
| lastscore | integer | The last score ascribed to this build record. This can be used in the UI among other places. |
|
| manual | boolean | NOT NULL DEFAULT false Indicates if the current record was or not rescored manually, if so it get skipped from the auto-score procedure. |
The persistent record of an import from a foreign version control system to Bazaar, from the initial request to the regularly updated import branch.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.branch.id | branch | integer | UNIQUE NOT NULL The Bazaar branch produced by the import system. Always non-NULL: a placeholder branch is created when the import is created. The import is associated to a Product and Series though the branch. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.person.id | registrant | integer | NOT NULL The person who originally requested this import. |
| rcs_type | integer | NOT NULL The revision control system used by the import source. The value is defined in dbschema.RevisionControlSystems. |
|
| svn_branch_url | text | The URL of the Subversion branch for this import. |
|
| cvs_root | text | The $CVSROOT details, probably of the form :pserver:user@host:/path. |
|
| cvs_module | text | The module in cvs_root to import, often the name of the project. |
|
| review_status | integer | NOT NULL DEFAULT 1 Whether this code import request has been reviewed, and whether it was accepted. |
|
| date_last_successful | timestamp without time zone | When this code import last succeeded. NULL if this import has never succeeded. |
|
| public.person.id | owner | integer | NOT NULL The person who is currently responsible for keeping the import details up to date, initially set to the registrant. This person can edit some of the details of the code import branch. |
| public.person.id | assignee | integer | The person in charge of delivering this code import and interacting with the owner. |
| update_interval | interval | How often should this import be updated. If NULL, defaults to a system-wide value set by the Launchpad administrators. |
|
| git_repo_url | text |
| Name | Constraint |
|---|---|
| valid_vcs_details | CHECK (CASE WHEN (rcs_type = 1) THEN ((((((cvs_root IS NOT NULL) AND (cvs_root <> ''::text)) AND (cvs_module IS NOT NULL)) AND (cvs_module <> ''::text)) AND (svn_branch_url IS NULL)) AND (git_repo_url IS NULL)) WHEN ((rcs_type = 2) OR (rcs_type = 3)) THEN (((((cvs_root IS NULL) AND (cvs_module IS NULL)) AND (svn_branch_url IS NOT NULL)) AND valid_absolute_url(svn_branch_url)) AND (git_repo_url IS NULL)) WHEN (rcs_type = 4) THEN ((((cvs_root IS NULL) AND (cvs_module IS NULL)) AND (svn_branch_url IS NULL)) AND (git_repo_url IS NOT NULL)) ELSE false END) |
Tables referencing this one via Foreign Key Constraints:
codeimport__assignee__idx assignee codeimport__owner__idx owner codeimport__registrant__idx registrantA record of events in the code import system. Rows in this table are created by triggers on other code import tables.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| entry_type | integer | NOT NULL The type of event that is recorded by this entry. Legal values are defined by the CodeImportEventType enumeration. |
|
| public.codeimport.id | code_import | integer | The code import that was associated to this event, if any and if it has not been deleted. |
| public.person.id | person | integer | The user who caused the event, if the event is not automatically generated. |
| public.codeimportmachine.id | machine | integer | The code import machine that was concerned by this event, if any. |
Tables referencing this one via Foreign Key Constraints:
codeimportevent__code_import__date_created__id__idx code_import, date_created, id codeimportevent__date_created__id__idx date_created, id codeimportevent__message__date_created__idx machine, date_created) WHERE (machine IS NOT NULL codeimportevent__person__idx person) WHERE (person IS NOT NULLAdditional data associated to a particular code import event.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.codeimportevent.id | event | integer | UNIQUE#1 The event the data is associated to. |
| data_type | integer | UNIQUE#1 NOT NULL The type of additional data, from the CodeImportEventDataType enumeration. |
|
| data_value | text | The value of the additional data. A string. |
A pending or active code import job. There is always such a row for any active import, but it will not run until date_due is in the past.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.codeimport.id | code_import | integer | UNIQUE NOT NULL The code import that is being worked upon. |
| public.codeimportmachine.id | machine | integer | The machine job is currently scheduled to run on, or where the job is currently running. |
| date_due | timestamp without time zone | NOT NULL When the import should happen. |
|
| state | integer | NOT NULL One of PENDING (waiting until its due or a machine is online), SCHEDULED (assigned to a machine, but not yet running) or RUNNING (actually in the process of being imported now). |
|
| public.person.id | requesting_user | integer | The user who requested the import, if any. Set if and only if reason = REQUEST. |
| ordering | integer | A measure of how urgent the job is -- queue entries with lower "ordering" should be processed first, or in other works "ORDER BY ordering" returns the most import jobs first. |
|
| heartbeat | timestamp without time zone | While the job is running, this field should be updated frequently to indicate that the import job hasn't crashed. |
|
| logtail | text | The last few lines of output produced by the running job. It should be updated at the same time as the heartbeat. |
|
| date_started | timestamp without time zone | When the import began to be processed. |
| Name | Constraint |
|---|---|
| valid_state | CHECK (CASE WHEN (state = 10) THEN (((((machine IS NULL) AND (ordering IS NULL)) AND (heartbeat IS NULL)) AND (date_started IS NULL)) AND (logtail IS NULL)) WHEN (state = 20) THEN (((((machine IS NOT NULL) AND (ordering IS NOT NULL)) AND (heartbeat IS NULL)) AND (date_started IS NULL)) AND (logtail IS NULL)) WHEN (state = 30) THEN (((((machine IS NOT NULL) AND (ordering IS NULL)) AND (heartbeat IS NOT NULL)) AND (date_started IS NOT NULL)) AND (logtail IS NOT NULL)) ELSE false END) |
The record of a machine capable of performing jobs for the code import system.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| hostname | text | UNIQUE NOT NULL The (unique) hostname of the machine. |
|
| state | integer | NOT NULL DEFAULT 10 Whether the controller daemon on this machine is offline, online, or quiescing (running but not accepting new jobs). |
|
| heartbeat | timestamp without time zone | When the code-import-controller daemon was last known to be running on this machine. If it is not updated for a long time the machine state will change to offline. |
Tables referencing this one via Foreign Key Constraints:
A completed code import job.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.codeimport.id | code_import | integer | The code import for which the job was run. |
| public.codeimportmachine.id | machine | integer | The machine the job ran on. |
| public.person.id | requesting_user | integer | |
| log_excerpt | text | The last few lines of the partial log, in case it is set. |
|
| public.libraryfilealias.id | log_file | integer | A partial log of the job for users to see. It is normally only recorded if the job failed in a step that interacts with the remote repository. If a job was successful, or failed in a houskeeping step, the log file would not contain information useful to the user. |
| status | integer | NOT NULL How the job ended. Success, some kind of failure, or some kind of interruption before completion. |
|
| date_job_started | timestamp without time zone | When the job started to run (date_created is when it finished). |
A message that is part of a code review discussion.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | UNIQUE#1 PRIMARY KEY | |
| public.branchmergeproposal.id | branch_merge_proposal | integer | UNIQUE#1 NOT NULL The merge proposal that is being discussed. |
| public.message.id | message | integer | UNIQUE NOT NULL The actual message. |
| vote | integer | The reviewer's vote for this message. |
|
| vote_tag | text | A short description of the vote |
Tables referencing this one via Foreign Key Constraints:
Reference to a person's last vote in a code review discussion.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.branchmergeproposal.id | branch_merge_proposal | integer | NOT NULL The BranchMergeProposal for the code review. |
| public.person.id | reviewer | integer | NOT NULL The person performing the review. |
| review_type | text | The aspect of the code being reviewed. |
|
| public.person.id | registrant | integer | NOT NULL The person who registered this vote |
| public.codereviewmessage.id | vote_message | integer | The message associated with the vote |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date this vote reference was created |
A Commercial Subscription entry for a project. Projects with licenses of Other/Proprietary must purchase a subscription in order to use Launchpad.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date this subscription was created in Launchpad. |
|
| date_last_modified | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date this subscription was last modified. |
|
| date_starts | timestamp without time zone | NOT NULL The beginning date for this subscription. It is invalid until that date. |
|
| date_expires | timestamp without time zone | NOT NULL The expiration date for this subscription. It is invalid after that date. |
|
| status | integer | NOT NULL DEFAULT 10 The current status. One of: SUBSCRIBED, LAPSED, SUSPENDED. |
|
| public.product.id | product | integer | NOT NULL The product this subscription enables. |
| public.person.id | registrant | integer | NOT NULL The person who created this subscription. |
| public.person.id | purchaser | integer | NOT NULL The person who purchased this subscription. |
| whiteboard | text | A place for administrators to store comments related to this subscription. |
|
| sales_system_id | text | A reference in the external sales system (e.g. Salesforce) that can be used to identify this subscription. |
Known components in Launchpad
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL Component name text |
|
| description | text | Description of this component. |
| Name | Constraint |
|---|---|
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
Allowed components in a given distroseries.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distroseries.id | distroseries | integer | UNIQUE#1 NOT NULL Refers to the distroseries in question. |
| public.component.id | component | integer | UNIQUE#1 NOT NULL Refers to the component in qestion. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
A continent in this huge world.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| code | text | UNIQUE NOT NULL A two-letter code for a continent. |
|
| name | text | UNIQUE NOT NULL The name of the continent. |
Tables referencing this one via Foreign Key Constraints:
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| iso3166code2 | character(2) | UNIQUE NOT NULL | |
| iso3166code3 | character(3) | UNIQUE NOT NULL | |
| name | text | UNIQUE NOT NULL | |
| title | text | ||
| description | text | ||
| public.continent.id | continent | integer | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
Overrides translation importer's interpretation of language codes where needed.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.product.id | product | integer | Product for which this custom language code applies (alternative to distribution + source package name). |
| public.distribution.id | distribution | integer | Distribution in which this custom language code applies (if not a product). |
| public.sourcepackagename.id | sourcepackagename | integer | Source package name to which this custom language code applies; goes with distribution. |
| language_code | text | NOT NULL Custom language code; need not be for a real language, and typically not for a "useful" language. |
|
| public.language.id | language | integer | Language to which code really refers in this context, or NULL if files with this code are to be rejected. |
| Name | Constraint |
|---|---|
| distro_and_sourcepackage | CHECK (((sourcepackagename IS NULL) = (distribution IS NULL))) |
| product_or_distro | CHECK (((product IS NULL) <> (distribution IS NULL))) |
A CVE Entry. The formal database of CVE entries is available at http://cve.mitre.org/ and we sync that database into Launchpad on a regular basis.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| sequence | text | UNIQUE NOT NULL The official CVE entry number. It takes the form XXXX-XXXX where the first four digits are a year indicator, like 2004, and the latter four are the sequence number of the vulnerability in that year. |
|
| status | integer | NOT NULL The current status of the CVE. The values are documented in dbschema.CVEState, and are Entry, Candidate, and Deprecated. |
|
| description | text | NOT NULL | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| datemodified | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The last time this CVE entry changed in some way - including addition or modification of references. |
|
| fti | ts2.tsvector |
| Name | Constraint |
|---|---|
| valid_cve_ref | CHECK (valid_cve(sequence)) |
Tables referencing this one via Foreign Key Constraints:
cve_datecreated_idx datecreated cve_datemodified_idx datemodified cve_fti ftiA reference in the CVE system that shows what outside tracking numbers are associated with the CVE. These are tracked in the CVE database and extracted from the daily XML dump that we fetch.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.cve.id | cve | integer | NOT NULL |
| source | text | NOT NULL The SOURCE of the CVE reference. This is a text string, like XF or BUGTRAQ or MSKB. Each string indicates a different kind of reference. The list of known types is documented on the CVE web site. At some future date we might turn this into an enum rather than a text, but for the moment we prefer to keep it fluid and just suck in what CVE gives us. This means that CVE can add new source types without us having to update our code. |
|
| content | text | NOT NULL The content of the ref in the CVE database. This is sometimes a comment, sometimes a description, sometimes a bug number... it is not predictable. |
|
| url | text | The URL to this reference out there on the web, if it was present in the CVE database. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Information common to static or preview diffs
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.libraryfilealias.id | diff_text | integer | The library copy of the fulltext of the diff |
| diff_lines_count | integer | The number of lines in the diff |
|
| diffstat | text | Statistics about the diff |
|
| added_lines_count | integer | The number of lines added in the diff. |
|
| removed_lines_count | integer | The number of lines removed in the diff |
Tables referencing this one via Foreign Key Constraints:
diff__diff_text__idx diff_textDistribution: A soyuz distribution. A distribution is a collection of DistroSeries. Distributions often group together policy and may be referred to by a name such as "Ubuntu" or "Debian"
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL The unique name of the distribution as a short lowercase name suitable for use in a URL. |
|
| title | text | NOT NULL The title of the distribution. More a "display name" as it were. E.g. "Ubuntu" or "Debian GNU/Linux" |
|
| description | text | NOT NULL A description of the distribution. More detailed than the title, this column may also contain information about the project this distribution is run by. |
|
| domainname | text | NOT NULL The domain name of the distribution. This may be used both for linking to the distribution and for context-related stuff. |
|
| public.person.id | owner | integer | NOT NULL The person in launchpad who is in ultimate-charge of this distribution within launchpad. |
| lucilleconfig | text | Configuration information which lucille will use when processing uploads and generating archives for this distribution |
|
| displayname | text | NOT NULL | |
| summary | text | NOT NULL | |
| public.person.id | members | integer | NOT NULL Person or team with upload and commit priviledges relating to this distribution. Other rights may be assigned to this role in the future. |
| public.translationgroup.id | translationgroup | integer | The translation group that is responsible for all translation work in this distribution. |
| translationpermission | integer | NOT NULL DEFAULT 1 The level of openness of this distribution's translation process. The enum lists different approaches to translation, from the very open (anybody can edit any translation in any language) to the completely closed (only designated translators can make any changes at all). |
|
| public.person.id | bug_supervisor | integer | Person who is responsible for managing bugs on this distribution. |
| official_malone | boolean | NOT NULL DEFAULT false Whether or not this distribution uses Malone for an official bug tracker. |
|
| official_rosetta | boolean | NOT NULL DEFAULT false Whether or not this distribution uses Rosetta for its official translation team and coordination. |
|
| public.person.id | security_contact | integer | The person or team who handles security-related issues in the distribution. |
| public.person.id | driver | integer | The team or person responsible for approving goals for each release in the distribution. This should usually be a very small team because the Distribution driver can approve items for backporting to past releases as well as the current release under development. Each distroseries has its own driver too, so you can have the small superset in the Distribution driver, and then specific teams per distroseries for backporting, for example, or for the current release management team on the current development focus release. |
| public.distroseries.id | translation_focus | integer | The DistroSeries that should get the translation effort focus. |
| public.person.id | mirror_admin | integer | NOT NULL Person or team with privileges to mark a mirror as official. |
| public.person.id | upload_admin | integer | Person foreign key which have access to modify the queue ui. If NULL, we fall back to launchpad admin members |
| upload_sender | text | The email address (and name) of the default sender used by the upload processor. If NULL, we fall back to the default sender in the launchpad config. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| homepage_content | text | A home page for this distribution in the Launchpad. |
|
| public.libraryfilealias.id | icon | integer | The library file alias to a small image to be used as an icon whenever we are referring to a distribution. |
| public.libraryfilealias.id | mugshot | integer | The library file alias of a mugshot image to display as the branding of a distribution, on its home page. |
| public.libraryfilealias.id | logo | integer | The library file alias of a smaller version of this distributions's mugshot. |
| fti | ts2.tsvector | ||
| official_answers | boolean | NOT NULL DEFAULT false Whether or not this product upstream uses Answers officialy. |
|
| public.person.id | language_pack_admin | integer | The Person or Team that handle language packs for the distro release. |
| official_blueprints | boolean | NOT NULL DEFAULT false | |
| enable_bug_expiration | boolean | NOT NULL DEFAULT false Indicates whether automatic bug expiration is enabled. |
|
| bug_reporting_guidelines | text | Guidelines to the end user for reporting bugs on this distribution. |
|
| reviewer_whiteboard | text | A whiteboard for Launchpad admins, registry experts and the project owners to capture the state of current issues with the project. |
| Name | Constraint |
|---|---|
| only_launchpad_has_expiration | CHECK (((enable_bug_expiration IS FALSE) OR (official_malone IS TRUE))) |
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
distribution__bug_supervisor__idx bug_supervisor) WHERE (bug_supervisor IS NOT NULL distribution__icon__idx icon) WHERE (icon IS NOT NULL distribution__language_pack_admin__idx language_pack_admin distribution__logo__idx logo) WHERE (logo IS NOT NULL distribution__mugshot__idx mugshot) WHERE (mugshot IS NOT NULL distribution_fti fti distribution_translationgroup_idx translationgroupThis table records a simple link between a bounty and a distribution. This bounty will be listed on the distribution web page, and the distribution will be mentioned on the bounty web page.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bounty.id | bounty | integer | UNIQUE#1 NOT NULL |
| public.distribution.id | distribution | integer | UNIQUE#1 NOT NULL |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
A mirror of a given distribution.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distribution.id | distribution | integer | NOT NULL The distribution to which the mirror refers to. |
| name | text | UNIQUE NOT NULL The unique name of the mirror. |
|
| http_base_url | text | UNIQUE The HTTP URL used to access the mirror. |
|
| ftp_base_url | text | UNIQUE The FTP URL used to access the mirror. |
|
| rsync_base_url | text | UNIQUE The Rsync URL used to access the mirror. |
|
| displayname | text | The displayname of the mirror. |
|
| description | text | A description of the mirror. |
|
| public.person.id | owner | integer | NOT NULL The owner of the mirror. |
| speed | integer | NOT NULL The speed of the mirror's Internet link. |
|
| public.country.id | country | integer | NOT NULL The country where the mirror is located. |
| content | integer | NOT NULL The content that is mirrored. |
|
| official_candidate | boolean | NOT NULL DEFAULT false Is the mirror a candidate for becoming an official mirror? |
|
| enabled | boolean | NOT NULL DEFAULT false Is this mirror enabled? |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date and time the mirror was created. |
|
| whiteboard | text | Notes on the current status of the mirror |
|
| status | integer | NOT NULL DEFAULT 10 This mirror's status. |
|
| date_reviewed | timestamp without time zone | The date and time the mirror was reviewed. |
|
| public.person.id | reviewer | integer | The person who reviewed the mirror. |
| Name | Constraint |
|---|---|
| one_or_more_urls | CHECK ((((http_base_url IS NOT NULL) OR (ftp_base_url IS NOT NULL)) OR (rsync_base_url IS NOT NULL))) |
| valid_ftp_base_url | CHECK (valid_absolute_url(ftp_base_url)) |
| valid_http_base_url | CHECK (valid_absolute_url(http_base_url)) |
| valid_name | CHECK (valid_name(name)) |
| valid_rsync_base_url | CHECK (valid_absolute_url(rsync_base_url)) |
Tables referencing this one via Foreign Key Constraints:
distributionmirror__country__status__idx country, status distributionmirror__status__idx statusRepresenting a sourcepackage in a distribution across all distribution series.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distribution.id | distribution | integer | UNIQUE#1 NOT NULL |
| public.sourcepackagename.id | sourcepackagename | integer | UNIQUE#1 NOT NULL |
| bug_reporting_guidelines | text | Guidelines to the end user for reporting bugs on a particular a source package in a distribution. |
A cache of the text associated with binary and source packages in the distribution. This table allows for fast queries to find a source packagename that matches a given text.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distribution.id | distribution | integer | UNIQUE#1 NOT NULL The distribution in which we are checking. |
| public.sourcepackagename.id | sourcepackagename | integer | UNIQUE#1 NOT NULL The source package name for which we are caching details. |
| name | text | The source package name itself. This is just a copy of the value of sourcepackagename.name. We have it here so it can be part of the full text index. |
|
| binpkgnames | text | The binary package names of binary packages generated from these source packages across all architectures. |
|
| binpkgsummaries | text | The aggregated summaries of all the binary packages generated from these source packages in this distribution. |
|
| binpkgdescriptions | text | The aggregated description of all the binary packages generated from these source packages in this distribution. |
|
| fti | ts2.tsvector | ||
| changelog | text | A concatenation of the source package release changelogs for this source package, where the status is not REMOVED. |
|
| public.archive.id | archive | integer | UNIQUE#1 NOT NULL The archive where the source is published. |
DistroArchSeries: A soyuz distribution release for a given architecture. A distroseries runs on various architectures. The distroarchseries groups that architecture-specific stuff.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distroseries.id | distroseries | integer | UNIQUE#1 UNIQUE#2 NOT NULL The distribution which this distroarchseries is part of. |
| public.processorfamily.id | processorfamily | integer | UNIQUE#2 NOT NULL A link to the ProcessorFamily table, giving the architecture of this DistroArchSeries. |
| architecturetag | text | UNIQUE#1 NOT NULL The name of this architecture in the context of this specific distro release. For example, some distributions might label amd64 as amd64, others might call is x86_64. This information is used, for example, in determining the names of the actual package files... such as the "amd64" part of "apache2_2.0.56-1_amd64.deb" |
|
| public.person.id | owner | integer | NOT NULL |
| official | boolean | NOT NULL Whether or not this architecture or "port" is an official release. If it is not official then you may not be able to install it or get all the packages for it. |
|
| package_count | integer | NOT NULL A cache of the number of binary packages published in this distro arch release. The count only includes packages published in the release pocket. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| supports_virtualized | boolean | NOT NULL DEFAULT false Whether or not virtualized build support should be provided by this specific distroarchseries |
Tables referencing this one via Foreign Key Constraints:
distroarchseries__distroseries__idx distroseries distroarchseries__owner__idx ownerDistroComponentUploader: A record of who can upload what to where. Distributions are permitted to have multiple components. Those components are often subject to different uploader constraints. This table represents those variable constraints by linking a team to a distribution,component tuple.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distribution.id | distribution | integer | UNIQUE#1 NOT NULL The distribution to which this upload permission applies. |
| public.component.id | component | integer | UNIQUE#1 NOT NULL The component to which this upload permission applies. |
| public.person.id | uploader | integer | NOT NULL The uploader(s) permitted to upload to the given component in the given distribution. This is commonly a team but may be a single person in the case of a simple distribution. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
DistroSeries: A soyuz distribution release. A DistroSeries is a given version of a distribution. E.g. "Warty" "Hoary" "Sarge" etc.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | UNIQUE#2 PRIMARY KEY | |
| public.distribution.id | distribution | integer | UNIQUE#1 UNIQUE#2 NOT NULL The distribution which contains this distroseries. |
| name | text | UNIQUE#1 NOT NULL The unique name of the distroseries. This is a short name in lower case and would be used in sources.list configuration and in generated URLs. E.g. "warty" "sarge" "sid" |
|
| title | text | NOT NULL The display-name title of the distroseries E.g. "Warty Warthog" |
|
| description | text | NOT NULL The long detailed description of the release. This may describe the focus of the release or other related information. |
|
| version | text | NOT NULL The version of the release. E.g. warty would be "4.10" and hoary would be "5.4" |
|
| releasestatus | integer | NOT NULL The current release status of this distroseries. E.g. "pre-release freeze" or "released" |
|
| datereleased | timestamp without time zone | The date on which this distroseries was released. (obviously only valid for released distributions) |
|
| public.distroseries.id public.distroseries.id | parent_series | integer | The parent distroseries on which this distribution is based. This is related to the inheritance stuff. |
| public.person.id | owner | integer | NOT NULL The ultimate owner of this distroseries. |
| lucilleconfig | text | Configuration information which lucille will use when processing uploads and generating archives for this distro release |
|
| summary | text | NOT NULL A brief summary of the distro release. This will be displayed in bold at the top of the distroseries page, above the distroseries description. It should include any high points that are particularly important to draw to the attention of users. |
|
| displayname | text | NOT NULL | |
| datelastlangpack | timestamp without time zone | ||
| messagecount | integer | NOT NULL This is a cached value and may be a few hours out of sync with reality. It should, however, be in sync with the values in DistroSeriesLanguage, and should never be updated separately. The total number of translation messages in this distro release, as per IRosettaStats. |
|
| public.distroarchseries.id | nominatedarchindep | integer | This is the DistroArchSeries nominated to build architecture independent packages within this DistroRelase, it is mandatory for buildable distroseries, i.e., Auto Build System will avoid to create build jobs for a DistroSeries with no nominatedarchindep, but the database model allow us to do it (for non-buildable DistroSeries). See further info in NominatedArchIndep specification. |
| changeslist | text | The email address (name name) of the changes announcement list for this distroseries. If NULL, no announcement mail will be sent. |
|
| binarycount | integer | NOT NULL A cache of the number of distinct binary package names published in this distro release. |
|
| sourcecount | integer | NOT NULL A cache of the number of distinct source package names published in this distro release. |
|
| public.person.id | driver | integer | This is a person or team who can act as a driver for this specific release - note that the distribution drivers can also set goals for any release. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| hide_all_translations | boolean | NOT NULL DEFAULT true Whether we should hid e all available translations for this distro release to non admin users. |
|
| defer_translation_imports | boolean | NOT NULL DEFAULT true Don't accept PO imports for this release just now. |
|
| public.languagepack.id | language_pack_base | integer | Current full export language pack for this distribution release. |
| public.languagepack.id | language_pack_delta | integer | Current language pack update based on language_pack_base information. |
| public.languagepack.id | language_pack_proposed | integer | Either a full or update language pack being tested to be used in language_pack_base or language_pack_delta. |
| language_pack_full_export_requested | boolean | NOT NULL DEFAULT false Whether next language pack export should be a full export or an update. |
| Name | Constraint |
|---|---|
| valid_language_pack_delta | CHECK (((language_pack_base IS NOT NULL) OR (language_pack_delta IS NULL))) |
| valid_name | CHECK (valid_name(name)) |
| valid_version | CHECK (sane_version(version)) |
Tables referencing this one via Foreign Key Constraints:
distroseries__driver__idx driver) WHERE (driver IS NOT NULL distroseries__owner__idx ownerA cache of the current translation status of that language across an entire distroseries.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distroseries.id | distroseries | integer | UNIQUE#1 |
| public.language.id public.language.id | language | integer | UNIQUE#1 |
| currentcount | integer | NOT NULL As per IRosettaStats. |
|
| updatescount | integer | NOT NULL As per IRosettaStats. |
|
| rosettacount | integer | NOT NULL As per IRosettaStats. |
|
| contributorcount | integer | NOT NULL The total number of contributors to the translation of this distroseries into this language. |
|
| dateupdated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The date these statistucs were last updated. |
|
| unreviewed_count | integer | NOT NULL As per IRosettaStats. |
A cache of the text associated with binary packages in the distroseries. This table allows for fast queries to find a binary packagename that matches a given text.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distroseries.id | distroseries | integer | UNIQUE#1 NOT NULL The distroseries in which we are checking. |
| public.binarypackagename.id | binarypackagename | integer | UNIQUE#1 NOT NULL The binary package name for which we are caching details. |
| name | text | The binary package name itself. This is just a copy of the value of binarypackagename.name. We have it here so it can be part of the full text index. |
|
| summary | text | A single summary for one of the binary packages of this name in this distroseries. We could potentially have binary packages in different architectures with the same name and different summaries, so this is a way of collapsing to one arbitrarily-chosen one, for display purposes. The chances of actually having different summaries and descriptions is pretty small. It could happen, though, because of the way package superseding works when a package does not build on a specific architecture. |
|
| description | text | ||
| summaries | text | The aggregated summaries of all the binary packages with this name in this distroseries. |
|
| descriptions | text | The aggregated description of all the binary packages with this name in this distroseries. |
|
| fti | ts2.tsvector | ||
| public.archive.id | archive | integer | UNIQUE#1 NOT NULL The archive where the binary is published. |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| text | NOT NULL An email address used by a Person. The email address is stored in a casesensitive way, but must be case insensitivly unique. |
||
| person | integer | ||
| status | integer | NOT NULL | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.account.id | account | integer |
| Name | Constraint |
|---|---|
| emailaddress__is_linked__chk | CHECK (((person IS NOT NULL) OR (account IS NOT NULL))) |
Entitlements and usage of privileged features.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | The person to which the entitlements apply. |
| entitlement_type | integer | NOT NULL The type of this entitlement (e.g. private bug). |
|
| quota | integer | NOT NULL Number of this entitlement allowed. |
|
| amount_used | integer | NOT NULL Quantity of this entitlement allocation that is used. |
|
| date_starts | timestamp without time zone | When this entitlement becomes active. |
|
| date_expires | timestamp without time zone | When this entitlement expires. |
|
| public.person.id | registrant | integer | The person (admin) who registered this entitlement. It is NULL if imported directly from an external sales system. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) Creation date of entitlement. |
|
| public.person.id | approved_by | integer | The person who approved this entitlement. It is NULL if imported directly from an external sales system. |
| date_approved | timestamp without time zone | Approval date of entitlement. It is NULL if imported directly from an external sales system. |
|
| state | integer | NOT NULL DEFAULT 30 The state (REQUESTED, ACTIVE, INACTIVE) of the entitlement. |
|
| whiteboard | text | A place for administrator notes. |
|
| is_dirty | boolean | NOT NULL DEFAULT true This entitlement has been modified and the state needst to be updated on the external system. |
|
| public.distribution.id | distribution | integer | The distribution to which this entitlement applies. |
| public.product.id | product | integer | The product to which this entitlement applies. |
| public.project.id | project | integer | The project to which this entitlement applies. |
| Name | Constraint |
|---|---|
| only_one_target | CHECK ((null_count(ARRAY[person, product, project, distribution]) = 3)) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| procpid | integer | ||
| usename | name | ||
| age | interval | ||
| relname | name | ||
| mode | text | ||
| granted | boolean | ||
| current_query | text |
SELECT alllocks.procpid , alllocks.usename , alllocks.age , alllocks.relname , alllocks.mode , alllocks.granted , alllocks.current_query FROM alllocks WHERE (alllocks.mode !~~ '%Share%'::text);
A technical document containing the answer to a common question.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY The FAQ document sequence number. |
|
| title | text | NOT NULL The document title. |
|
| tags | text | White-space separated list of tags. |
|
| content | text | NOT NULL The content of FAQ. It can also contain a short summary and a link. |
|
| public.product.id | product | integer | The product to which this document is related. Either "product" or "distribution" must be set. |
| public.distribution.id | distribution | integer | The distribution to which this document is related. Either "product" or "distribution" must be set. |
| public.person.id | owner | integer | NOT NULL The person who created the document. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The datetime when the document was created. |
|
| public.person.id | last_updated_by | integer | The person who last modified the document. |
| date_last_updated | timestamp without time zone | The datetime when the document was last modified. |
|
| fti | ts2.tsvector |
| Name | Constraint |
|---|---|
| product_or_distro | CHECK (((product IS NULL) <> (distribution IS NULL))) |
Tables referencing this one via Foreign Key Constraints:
faq__distribution__idx distribution) WHERE (distribution IS NOT NULL faq__product__idx product) WHERE (product IS NOT NULL faq_fti ftiA list of featured projects. This table is really just a list of pillarname IDs, if a project's pillar name is in this list then it is a featured project and will be listed on the Launchpad home page.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.pillarname.id | pillar_name | integer | NOT NULL A reference to PillarName.id |
In order to facilitate the querying of set-subset relationships an expanded or flattened representation of the set-subset hierarchy is provided by this table.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.packageset.id | parent | integer | UNIQUE#1 NOT NULL The package set that is (directly or indirectly) including a subset. |
| public.packageset.id | child | integer | UNIQUE#1 NOT NULL The package set that is being included as a subset. |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| tablename | text | UNIQUE NOT NULL | |
| columns | text | NOT NULL |
A GPG key belonging to a Person
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | UNIQUE#1 PRIMARY KEY | |
| public.person.id | owner | integer | UNIQUE#1 NOT NULL |
| keyid | text | NOT NULL The 8 character GPG key id, uppercase and no whitespace |
|
| fingerprint | text | UNIQUE NOT NULL The 40 character GPG fingerprint, uppercase and no whitespace |
|
| active | boolean | NOT NULL True if this key is active for use in Launchpad context, false could be deactivated by user or revoked in the global key ring. |
|
| algorithm | integer | NOT NULL The algorithm used to generate this key. Valid values defined in dbschema.GPGKeyAlgorithms |
|
| keysize | integer | NOT NULL Size of the key in bits, as reported by GPG. We may refuse to deal with keysizes < 768 bits in the future. |
|
| can_encrypt | boolean | NOT NULL DEFAULT false Whether the key has been validated for use in encryption (as opposed to just signing) |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
| Name | Constraint |
|---|---|
| valid_fingerprint | CHECK (valid_fingerprint(fingerprint)) |
| valid_keyid | CHECK (valid_keyid(keyid)) |
Tables referencing this one via Foreign Key Constraints:
Basic information on devices.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwvendorid.id | bus_vendor_id | integer | UNIQUE#1 NOT NULL A reference to a HWVendorID record. |
| bus_product_id | text | UNIQUE#1 NOT NULL The bus product ID of a device |
|
| variant | text | UNIQUE#1 An optional additional description for a device that shares its vendor and product ID with another, technically different, device. |
|
| name | text | NOT NULL The human readable product name of the device. |
|
| submissions | integer | NOT NULL The number of submissions that contain this device. |
Tables referencing this one via Foreign Key Constraints:
hwdevice__bus_product_id__idx bus_product_id hwdevice__name__idx nameCapabilities of a device.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwdevice.id | device | integer | NOT NULL A reference to a device. |
| main_class | integer | NOT NULL The main class of a device. Legal values are defined by the HWMainClass enumeration. |
|
| sub_class | integer | The sub-class of a device. Legal values are defined by the HWSubClass enumeration. |
Combinations of devices and drivers mentioned in submissions.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwdevice.id | device | integer | NOT NULL The device controlled by the driver. |
| public.hwdriver.id | driver | integer | The driver controlling the device. |
Tables referencing this one via Foreign Key Constraints:
hwdevicedriverlink__device__idx device hwdevicedriverlink__driver__idx driverAlternative vendor and product names of devices.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwvendorname.id | vendor_name | integer | UNIQUE#1 NOT NULL The alternative vendor name. |
| product_name | text | UNIQUE#1 NOT NULL The alternative product name. |
|
| public.hwdevice.id | device | integer | UNIQUE#1 NOT NULL The device named by this alternative vendor and product names. |
| submissions | integer | NOT NULL The number of submissions containing this alternative vendor and product name. |
A DMI Handle appearing in the DMI data of a submission.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| handle | integer | NOT NULL The ID of the handle. |
|
| type | integer | NOT NULL The type of the handle. |
|
| public.hwsubmission.id | submission | integer |
Tables referencing this one via Foreign Key Constraints:
hwdmihandle__submission__idx submissionKey/value pairs of DMI data of a handle.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| key | text | The key. |
|
| value | text | The value |
|
| public.hwdmihandle.id | handle | integer | NOT NULL The handle to which this key/value pair belongs. |
Information about a driver for a device
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| package_name | text | UNIQUE#1 The Debian package name a driver is a part of |
|
| name | text | UNIQUE#1 NOT NULL The name of a driver. |
|
| license | integer |
Tables referencing this one via Foreign Key Constraints:
hwdriver__name__idx name hwvendorname__name__idx nameA view returning the distinct driver names stored in HWDriver.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | integer | ||
| name | text | The name of a driver. |
SELECT DISTINCT ON (hwdriver.name) hwdriver.id , hwdriver.name FROM hwdriver ORDER BY hwdriver.name , hwdriver.id;
A view returning the distinct Debian package names stored in HWDriver.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | integer | ||
| package_name | text | The Debian package name a driver is a part of. |
SELECT DISTINCT ON (hwdriver.package_name) hwdriver.id , hwdriver.package_name FROM hwdriver ORDER BY hwdriver.package_name , hwdriver.id;
Raw HWDB submission data
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL Date and time of the submission (generated by the client). |
|
| date_submitted | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) Date and time of the submission (generated by the server). |
|
| format | integer | NOT NULL The format version of the submitted data, as given by the HWDB client. See HWSubmissionFormat for valid values. |
|
| status | integer | NOT NULL DEFAULT 1 The status of the submission. See HWSubmissionProcessingStatus for valid values. |
|
| private | boolean | NOT NULL If false, the submitter allows public access to the data. If true, the data may be used only for statistical purposes. |
|
| contactable | boolean | NOT NULL If True, the submitter agrees to be contacted by upstream developers and package maintainers for tests etc. |
|
| submission_key | text | UNIQUE NOT NULL A unique submission ID. |
|
| public.person.id | owner | integer | A reference to the Person table: The owner/submitter of the data. |
| public.distroarchseries.id | distroarchseries | integer | A reference to the distroarchseries of the submission. This value is null, if the submitted values for distribution, distroseries and architecture do not match an existing entry in the Distroarchseries table. |
| public.libraryfilealias.id | raw_submission | integer | NOT NULL A reference to a row of LibraryFileAlias. The library file contains the raw submission data. |
| public.hwsystemfingerprint.id | system_fingerprint | integer | NOT NULL A reference to an entry of the HWDBSystemFingerPrint table. This table stores the system name as returned by HAL (system.vendor, system.product) |
| raw_emailaddress | text | The email address of the submitter. |
Tables referencing this one via Foreign Key Constraints:
hwsubmission__lower_raw_emailaddress__idx lower(raw_emailaddress) hwsubmission__owner__idx owner hwsubmission__raw_emailaddress__idx raw_emailaddress hwsubmission__raw_submission__idx raw_submission hwsubmission__status__idx status hwsubmission__system_fingerprint__idx system_fingerprintLink bugs to HWDB submissions
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwsubmission.id | submission | integer | UNIQUE#1 NOT NULL |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL |
Links between devices and submissions.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwdevicedriverlink.id | device_driver_link | integer | NOT NULL The combination (device, driver) mentioned in a submission. |
| public.hwsubmission.id | submission | integer | NOT NULL The submission mentioning this (device, driver) combination. |
| public.hwsubmissiondevice.id | parent | integer | The parent device of this device. |
| hal_device_id | integer | NOT NULL The ID of the HAL node of this device in the submitted data. |
Tables referencing this one via Foreign Key Constraints:
hwsubmissiondevice__device_driver_link__idx device_driver_link hwsubmissiondevice__parent__idx parent) WHERE (parent IS NOT NULL hwsubmissiondevice__submission__idx submissionA distinct list of "fingerprints" (HAL system.name, system.vendor) from raw submission data
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| fingerprint | text | UNIQUE NOT NULL The fingerprint |
Tables referencing this one via Foreign Key Constraints:
General information about a device test.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| namespace | text | The namespace of a test. |
|
| name | text | NOT NULL The name of a test. |
|
| version | text | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
The answer for a test from a submission. This can be either a multiple choice selection or a numerical value. Exactly one of the columns choice, intval, floatval must be non-null.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwtestanswerchoice.test#1 public.hwtest.id | test | integer | NOT NULL The test answered by this answer. |
| public.hwtestanswerchoice.id#1 public.hwtestanswerchoice.id | choice | integer | The selected value of a multiple choice test. |
| intval | integer | The integer result of a test with a numerical result. |
|
| floatval | double precision | The double precision floating point number result of a test with a numerical result. |
|
| unit | text | The physical unit of a test with a numerical result. |
|
| comment | text | ||
| public.language.id | language | integer | |
| public.hwsubmission.id | submission | integer | NOT NULL |
| Name | Constraint |
|---|---|
| hwtestanswer_check | CHECK (((((choice IS NULL) AND (unit IS NOT NULL)) AND ((intval IS NULL) <> (floatval IS NULL))) OR ((((choice IS NOT NULL) AND (unit IS NULL)) AND (intval IS NULL)) AND (floatval IS NULL)))) |
Tables referencing this one via Foreign Key Constraints:
hwtestanswer__choice__idx choice hwtestanswer__submission__idx submission hwtestanswer__test__idx testChoice values of multiple choice tests/questions.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | UNIQUE#2 PRIMARY KEY | |
| choice | text | UNIQUE#1 NOT NULL The choice value. |
|
| public.hwtest.id | test | integer | UNIQUE#1 UNIQUE#2 NOT NULL The test this choice belongs to. |
Tables referencing this one via Foreign Key Constraints:
hwtestanswerchoice__test__idx testAccumulated results of tests. Either the column choice or the columns average and sum_square must be non-null.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwtest.id | test | integer | NOT NULL The test. |
| public.distroarchseries.id | distroarchseries | integer | The distroarchseries for which results are accumulated, |
| public.hwtestanswerchoice.id | choice | integer | The choice value of a multiple choice test. |
| average | double precision | The average value of the result of a numerical test. |
|
| sum_square | double precision | The sum of the squares of the results of a numerical test. |
|
| unit | text | The physical unit of a numerical test result. |
|
| num_answers | integer | NOT NULL The number of submissions from which the result is accumulated. |
| Name | Constraint |
|---|---|
| hwtestanswercount_check | CHECK ((((((choice IS NULL) AND (average IS NOT NULL)) AND (sum_square IS NOT NULL)) AND (unit IS NOT NULL)) OR ((((choice IS NOT NULL) AND (average IS NULL)) AND (sum_square IS NULL)) AND (unit IS NULL)))) |
Tables referencing this one via Foreign Key Constraints:
hwtestanswercount__choice__idx choice hwtestanswercount__distroarchrelease__idx distroarchseries) WHERE (distroarchseries IS NOT NULL hwtestanswercount__test__idx testAssociation of accumulated test results and device/driver combinations.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwtestanswercount.id | answer | integer | UNIQUE#1 NOT NULL The test answer. |
| public.hwdevicedriverlink.id | device_driver | integer | UNIQUE#1 NOT NULL The device/driver combination. |
Association of test results and device/driver combinations.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.hwtestanswer.id | answer | integer | UNIQUE#1 NOT NULL The test answer. |
| public.hwdevicedriverlink.id | device_driver | integer | UNIQUE#1 NOT NULL The device/driver combination. |
Associates tuples (bus, vendor ID for this bus) with vendor names.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| bus | integer | UNIQUE#1 NOT NULL The bus. |
|
| vendor_id_for_bus | text | UNIQUE#1 NOT NULL The ID of a vendor for the bus given by column `bus` |
|
| public.hwvendorname.id | vendor_name | integer | UNIQUE#1 NOT NULL |
Tables referencing this one via Foreign Key Constraints:
hwvendorid__vendor_id_for_bus__idx vendor_id_for_bus hwvendorid__vendorname__idx vendor_nameA list of hardware vendor names.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | NOT NULL The name of a vendor. |
Tables referencing this one via Foreign Key Constraints:
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | NOT NULL |
| network | text | NOT NULL | |
| nickname | text | NOT NULL |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | NOT NULL |
| jabberid | text | UNIQUE NOT NULL |
Common info about a job.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | UNIQUE#1 PRIMARY KEY | |
| public.person.id | requester | integer | Ther person who requested this job (if applicable). |
| reason | text | The reason that this job was created (if applicable) |
|
| status | integer | UNIQUE#1 NOT NULL An enum (JobStatus) indicating the job status, one of: new, in-progress, complete, failed, cancelling, cancelled. |
|
| progress | integer | The percentage complete. Can be NULL for some jobs that do not report progress. |
|
| last_report_seen | timestamp without time zone | The last time the progress was reported. |
|
| next_report_due | timestamp without time zone | The next time a progress report is expected. |
|
| attempt_count | integer | NOT NULL The number of times this job has been attempted. |
|
| max_retries | integer | NOT NULL The maximum number of retries valid for this job. |
|
| log | text | If provided, this is the tail of the log file being generated by the running job. |
|
| scheduled_start | timestamp without time zone | The time when the job should start |
|
| lease_expires | timestamp without time zone | The time when the lease expires. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The time when the job was created. |
|
| date_started | timestamp without time zone | If the job has started, the time when the job started. |
|
| date_finished | timestamp without time zone | If the job has finished, the time when the job finished. |
Tables referencing this one via Foreign Key Constraints:
job__requester__key requester) WHERE (requester IS NOT NULLUsed to quantify all the 'operations' a user performs inside the system, which maybe reporting and fixing bugs, uploading packages, end-user support, wiki editting, etc.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) A timestamp for the assignment of this Karma. |
|
| public.person.id | person | integer | NOT NULL The Person for wich this Karma was assigned. |
| public.karmaaction.id | action | integer | NOT NULL A foreign key to the KarmaAction table. |
| public.product.id | product | integer | The Project to which this Product belongs. An entry on this table with a non-NULL Project and a NULL Product represents the total karma of the person across all products of that project.. |
| public.distribution.id | distribution | integer | The Distribution on which a person performed an action that resulted on this karma. |
| public.sourcepackagename.id | sourcepackagename | integer | The SourcePackageName on which a person performed an action that resulted on this karma. |
Stores all the actions that would give karma to the user which performed it.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.karmacategory.id | category | integer | A dbschema value used to group actions together. |
| points | integer | The number of points this action is worth of. |
|
| name | text | UNIQUE NOT NULL The unique name of this action. |
|
| title | text | NOT NULL | |
| summary | text | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
Stores a cached value of a person's karma points, grouped by the action category and the context where that action was performed.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | NOT NULL The person which performed the actions of this category, and thus got the karma. |
| category | integer | The category of the actions. |
|
| karmavalue | integer | NOT NULL The karma points of all actions of this category performed by this person on this context (product/distribution). |
|
| public.product.id | product | integer | |
| public.distribution.id | distribution | integer | |
| public.sourcepackagename.id | sourcepackagename | integer | |
| public.project.id | project | integer |
| Name | Constraint |
|---|---|
| just_distribution | CHECK (((distribution IS NULL) OR ((product IS NULL) AND (project IS NULL)))) |
| just_product | CHECK (((product IS NULL) OR ((project IS NULL) AND (distribution IS NULL)))) |
| just_project | CHECK (((project IS NULL) OR ((product IS NULL) AND (distribution IS NULL)))) |
| sourcepackagename_requires_distribution | CHECK (((sourcepackagename IS NULL) OR (distribution IS NOT NULL))) |
A category of karma. This allows us to present an overall picture of the different areas where a user has been active.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | NOT NULL | |
| title | text | NOT NULL | |
| summary | text | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | UNIQUE NOT NULL |
| karma_total | integer | NOT NULL |
A human language.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| code | text | UNIQUE NOT NULL The ISO 639 code for this language |
|
| englishname | text | The english name for this language |
|
| nativename | text | The name of this language in the language itself |
|
| pluralforms | integer | The number of plural forms this language has |
|
| pluralexpression | text | The plural expression for this language, as used by gettext |
|
| visible | boolean | NOT NULL Whether this language should usually be visible or not |
|
| direction | integer | NOT NULL The direction that text is written in this language |
|
| uuid | text | Mozilla language pack unique ID |
| Name | Constraint |
|---|---|
| valid_language | CHECK (((pluralforms IS NULL) = (pluralexpression IS NULL))) |
Tables referencing this one via Foreign Key Constraints:
Store exported language packs for DistroSeries.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.libraryfilealias.id | file | integer | NOT NULL Librarian file where the language pack is stored. |
| date_exported | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) When was exported the language pack. |
|
| date_last_used | timestamp without time zone | DEFAULT timezone('UTC'::text, now()) When did we stop using the language pack. It's used to decide whether we can remove it completely from the system. When it's being used, its value is NULL |
|
| public.distroseries.id | distroseries | integer | NOT NULL The distribution series from where this language pack was exported. |
| type | integer | NOT NULL DEFAULT 1 Type of language pack. There are two types available, 1: Full export, 2: Update export based on language_pack_that_updates export. |
|
| public.languagepack.id | updates | integer | The LanguagePack that this one updates. |
| Name | Constraint |
|---|---|
| valid_updates | CHECK ((((type = 2) AND (updates IS NOT NULL)) OR ((type = 1) AND (updates IS NULL)))) |
Tables referencing this one via Foreign Key Constraints:
languagepack__file__idx fileThis table contains a list of the database patches that have been successfully applied to this database.
| F-Key | Name | Type | Description |
|---|---|---|---|
| major | integer | PRIMARY KEY Major number. This is the version of the baseline schema the patch was made agains. |
|
| minor | integer | PRIMARY KEY Minor number. Patches made during development each increment the minor number. |
|
| patch | integer | PRIMARY KEY The patch number will hopefully always be '0', as it exists to support emergency patches made to the production server. eg. If production is running '4.0.0' and needs to have a patch applied ASAP, we would create a '4.0.1' patch and roll it out. We then may need to refactor all the existing '4.x.0' patches. |
A store of system-wide statistics or other integer values, keyed by names. The names are unique and the values can be any integer. Each field has a place to store the timestamp when it was last updated, so it is possible to know how far out of date any given statistic is.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL | |
| value | integer | NOT NULL | |
| dateupdated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) |
LibraryFileAlias: A librarian file's alias. The librarian stores, along with the file contents, a record stating the file name and mimetype. This table represents it.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.libraryfilecontent.id | content | integer | NOT NULL The libraryfilecontent which is the data in this file. |
| filename | text | NOT NULL The name of the file. E.g. "foo_1.0-1_i386.deb" |
|
| mimetype | text | NOT NULL The mime type of the file. E.g. "application/x-debian-package" |
|
| expires | timestamp without time zone | The expiry date of this file. If NULL, this item may be removed as soon as it is no longer referenced. If set, the item will not be removed until this date. Once the date is passed, the file may be removed from disk even if this item is still being referenced (in which case content.deleted will be true) |
|
| last_accessed | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) Roughly when this file was last retrieved from the Librarian. Initially set to this item's creation date. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The timestamp when this alias was created. |
|
| restricted | boolean | NOT NULL DEFAULT false Is this file available only from the restricted librarian? |
|
| hits | integer | NOT NULL The number of times this file has been downloaded. |
| Name | Constraint |
|---|---|
| valid_filename | CHECK ((filename !~~ '%/%'::text)) |
Tables referencing this one via Foreign Key Constraints:
libraryfilealias__expires__idx expires libraryfilealias__filename__idx filename libraryfilealias_content_idx contentLibraryFileContent: A librarian file's contents. The librarian stores files in a safe and transactional way. This table represents the contents of those files within the database.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The date on which this librarian file was created |
|
| datemirrored | timestamp without time zone | When the file was mirrored from the librarian onto the backup server |
|
| filesize | integer | NOT NULL The size of the file |
|
| sha1 | character(40) | NOT NULL The SHA1 sum of the file's contents |
|
| deleted | boolean | NOT NULL DEFAULT false This file has been removed from disk by the librarian garbage collector. |
|
| md5 | character(32) | NOT NULL The MD5 sum of the file's contents |
|
| sha256 | character(64) | The SHA256 sum of the file's contents |
Tables referencing this one via Foreign Key Constraints:
libraryfilecontent__md5__idx md5 libraryfilecontent__sha256__idx sha256 libraryfilecontent_sha1_filesize_idx sha1, filesizeThe number of daily downloads for a given LibraryFileAlias.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.libraryfilealias.id | libraryfilealias | integer | UNIQUE#1 NOT NULL The LibraryFileAlias. |
| day | date | UNIQUE#1 NOT NULL The day of the downloads. |
|
| count | integer | NOT NULL The number of downloads. |
|
| public.country.id | country | integer | UNIQUE#1 The country from where the download requests came from. |
LoginToken stores one time tokens used by Launchpad for validating email addresses and other tasks that require verifying an email address is valid such as password recovery and account merging. This table will be cleaned occasionally to remove expired tokens. Expiry time is not yet defined.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | requester | integer | The Person that made this request. This will be null for password recovery requests. |
| requesteremail | text | The email address that was used to login when making this request. This provides an audit trail to help the end user confirm that this is a valid request. It is not a link to the EmailAddress table as this may be changed after the request is made. This field will be null for password recovery requests. |
|
| text | NOT NULL The email address that this request was sent to. |
||
| created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The timestamp that this request was made. |
|
| tokentype | integer | NOT NULL The type of request, as per dbschema.TokenType. |
|
| token | text | UNIQUE The token (not the URL) emailed used to uniquely identify this request. This token will be used to generate a URL that when clicked on will continue a workflow. |
|
| fingerprint | text | The GPG key fingerprint to be validated on this transaction, it means that a new register will be created relating this given key with the requester in question. The requesteremail still passing for the same usual checks. |
|
| redirection_url | text | ||
| date_consumed | timestamp without time zone | The date and time when this token was consumed. It's NULL if it hasn't been consumed yet. |
| Name | Constraint |
|---|---|
| valid_fingerprint | CHECK (((fingerprint IS NULL) OR valid_fingerprint(fingerprint))) |
The mailing list for a team. Teams may have zero or one mailing list, and a mailing list is associated with exactly one team. This table manages the state changes that a team mailing list can go through, and it contains information that will be used to instruct Mailman how to create, delete, and modify mailing lists (via XMLRPC).
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | team | integer | UNIQUE NOT NULL The team this mailing list is associated with. |
| public.person.id | registrant | integer | NOT NULL The id of the Person who requested this list be created. |
| date_registered | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) Date the list was requested to be created |
|
| public.person.id | reviewer | integer | The id of the Person who reviewed the creation request, or NULL if not yet reviewed. |
| date_reviewed | timestamp without time zone | DEFAULT timezone('UTC'::text, now()) The date the request was reviewed, or NULL if not yet reviewed. |
|
| date_activated | timestamp without time zone | DEFAULT timezone('UTC'::text, now()) The date the list was (last) activated. If the list is not yet active, this field will be NULL. |
|
| status | integer | NOT NULL DEFAULT 1 The current status of the mailing list, as a dbschema.MailingListStatus value. |
|
| welcome_message | text | Text sent to new members when they are subscribed to the team list. If NULL, no welcome message is sent. |
Tables referencing this one via Foreign Key Constraints:
mailinglist__date_registered__idx status, date_registered mailinglist__registrant__idx registrant mailinglist__reviewer__idx reviewerTrack explicit Launchpad-wide posting bans imposed on people by Launchpad administrators.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | NOT NULL The person who was banned. |
| public.person.id | banned_by | integer | NOT NULL The administrator who imposed the ban. |
| date_banned | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) When the ban was imposed. |
|
| reason | text | NOT NULL The reason for the ban. |
Track the subscriptions of a person to team mailing lists.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | NOT NULL The person who is subscribed to the mailing list. |
| public.mailinglist.id | mailing_list | integer | NOT NULL The mailing list this person is subscribed to. |
| date_joined | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date this person subscribed to the mailing list. |
|
| email_address | integer | Which of the person's email addresses are subscribed to the mailing list. This may be NULL to indicate that it's the person's preferred address. |
An offer to provide mentoring if someone wa nts to help get a specific bug fixed or blueprint implemented. These offers are specifically associated with a team in which the offeror is a member, so it beco mes possible to encourage people who want to join a team to start by working on things that existing team members are willing to mentor.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | owner | integer | UNIQUE#1 UNIQUE#2 NOT NULL |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.person.id | team | integer | NOT NULL This is the team to which this offer of mentoring is associated. We associate each offer of mentoring with a team, de signated as "the team which will most benefit from the bug fix or spec implement ation", and this then allows us to provide a list of work for which mentoring is available for prospective members of those teams. This is really the "onramp" i dea - the list is the "onramp" to membership in the relevant team. |
| public.bug.id | bug | integer | UNIQUE#1 |
| public.specification.id | specification | integer | UNIQUE#2 |
| Name | Constraint |
|---|---|
| context_required | CHECK (((bug IS NULL) <> (specification IS NULL))) |
A job to process a merge directive.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.job.id | job | integer | UNIQUE NOT NULL The job associated with this MergeDirectiveJob. |
| public.libraryfilealias.id | merge_directive | integer | NOT NULL Full MIME content of the message containing the merge directive. |
| action | integer | NOT NULL Enumeration of the action to perform with the merge directive; push or create merge proposal. |
This table stores a single RFC822-style message. Messages can be threaded (using the parent field). These messages can then be referenced from elsewhere in the system, such as the BugMessage table, integrating messageboard facilities with the rest of The Launchpad.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| subject | text | The title text of the message, or the subject if it was an email. |
|
| public.person.id | owner | integer | |
| public.message.id | parent | integer | A "parent message". This allows for some level of threading in Messages. |
| public.distribution.id | distribution | integer | The distribution in which this message originated, if we know it. |
| rfc822msgid | text | NOT NULL | |
| fti | ts2.tsvector | ||
| public.libraryfilealias.id | raw | integer | The original unadulterated message if it arrived via email. This is required to provide access to the original, undecoded message. |
Tables referencing this one via Foreign Key Constraints:
message_fti fti message_owner_idx owner message_parent_idx parent message_raw_idx raw) WHERE (raw IS NOT NULL message_rfc822msgid_idx rfc822msgidTrack mailing list postings awaiting approval from the team owner.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | posted_by | integer | NOT NULL The person who posted the message. |
| public.mailinglist.id | mailing_list | integer | NOT NULL The mailing list to which the message was posted. |
| public.libraryfilealias.id | posted_message | integer | NOT NULL Foreign key to libraryfilealias table pointing to where the posted message's text lives. |
| posted_date | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date the message was posted. |
|
| status | integer | NOT NULL The status of the posted message. Values are described in dbschema.PostedMessageStatus. |
|
| public.person.id | disposed_by | integer | The person who disposed of (i.e. approved or rejected) the message, or NULL if no disposition has yet been made. |
| disposal_date | timestamp without time zone | DEFAULT timezone('UTC'::text, now()) The date on which this message was disposed, or NULL if no disposition has yet been made. |
|
| reason | text | The reason for the current status if any. This information will be displayed to the end user and mailing list moderators need to be aware of this - not a private whiteboard. |
|
| public.message.id | message | integer | NOT NULL Foreign key to message table pointing to the posted message. |
This table stores a single chunk of a possibly multipart message. There will be at least one row in this table for each message. text/* parts are stored in the content column. All other parts are stored in the Librarian and referenced via the blob column. If both content and blob are NULL, then this chunk has been removed (eg. offensive, legal reasons, virus etc.)
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.message.id | message | integer | UNIQUE#1 NOT NULL |
| sequence | integer | UNIQUE#1 NOT NULL Order of a particular chunk. Chunks are orders in ascending order starting from 1. |
|
| content | text | Text content for this chunk of the message. This content is full text searchable. |
|
| public.libraryfilealias.id | blob | integer | Binary content for this chunk of the message. |
| fti | ts2.tsvector |
| Name | Constraint |
|---|---|
| text_or_content | CHECK ((((blob IS NULL) AND (content IS NULL)) OR ((blob IS NULL) <> (content IS NULL)))) |
An identifier that helps a maintainer group together things in some way, e.g. "1.2" could be a Milestone that bazaar developers could use to mark a task as needing fixing in bazaar 1.2.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | UNIQUE#1 PRIMARY KEY UNIQUE#4 | |
| public.product.id public.productseries.product#2 | product | integer | UNIQUE#3 UNIQUE#4 The product for which this is a milestone. |
| name | text | UNIQUE#2 UNIQUE#3 NOT NULL The identifier text, e.g. "1.2." |
|
| public.distroseries.distribution#1 public.distribution.id | distribution | integer | UNIQUE#1 UNIQUE#2 The distribution to which this milestone belongs, if it is a distro milestone. |
| dateexpected | timestamp without time zone | If set, the date on which we expect this milestone to be delivered. This allows for optional sorting by date. |
|
| active | boolean | NOT NULL DEFAULT true Whether or not this milestone should be displayed in general listings. All milestones will be visible on the "page of milestones for product foo", but we want to be able to screen out obviously old milestones over time, for the general listings and vocabularies. |
|
| public.productseries.id#2 public.productseries.id | productseries | integer | The productseries for which this is a milestone. A milestone on a productseries is ALWAYS also a milestone for the same product. This is because milestones started out on products/distributions but are moving to being on series/distroseries. |
| public.distroseries.id#1 public.distroseries.id | distroseries | integer | The distroseries for which this is a milestone. A milestone on a distroseries is ALWAYS also a milestone for the same distribution. This is because milestones started out on products/distributions but are moving to being on series/distroseries. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| summary | text | This can be used to summarize the changes included in past milestones and to document the status of current milestones. |
|
| codename | text | A fun or easier to remember name for the milestone/release. |
| Name | Constraint |
|---|---|
| valid_name | CHECK (valid_name(name)) |
| valid_target | CHECK ((NOT ((product IS NULL) AND (distribution IS NULL)))) |
Tables referencing this one via Foreign Key Constraints:
Stores general information about mirror sites. Both regular pull mirrors and top tier mirrors are included.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | owner | integer | NOT NULL |
| baseurl | text | NOT NULL The base URL to the mirror, including protocol and optional trailing slash. |
|
| public.country.id | country | integer | NOT NULL The country where the mirror is located. |
| name | text | UNIQUE NOT NULL Unique name for the mirror, suitable for use in URLs. |
|
| description | text | NOT NULL Description of the mirror. |
|
| freshness | integer | NOT NULL DEFAULT 99 dbschema.MirrorFreshness enumeration indicating freshness. |
|
| lastcheckeddate | timestamp without time zone | UTC timestamp of when the last check for freshness and consistency was made. NULL indicates no check has ever been made. |
|
| approved | boolean | NOT NULL DEFAULT false True if this mirror has been approved by the Ubuntu/Canonical mirror manager, otherwise False. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Tables referencing this one via Foreign Key Constraints:
The mirror of a given CD/DVD image.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distributionmirror.id | distribution_mirror | integer | UNIQUE#1 NOT NULL The distribution mirror. |
| public.distroseries.id | distroseries | integer | UNIQUE#1 NOT NULL The Distribution Release. |
| flavour | text | UNIQUE#1 NOT NULL The Distribution Release Flavour. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Stores which distroarchseries and compoenents a given mirror has.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.mirror.id | mirror | integer | NOT NULL |
| public.distroarchseries.id | distroarchseries | integer | NOT NULL A distroarchseries that this mirror contains. |
| public.component.id | component | integer | NOT NULL What component of the distroarchseries that this mirror contains. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
The mirror of the packages of a given Distro Arch Release.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distributionmirror.id | distribution_mirror | integer | NOT NULL The distribution mirror. |
| public.distroarchseries.id | distroarchseries | integer | NOT NULL The distro arch series. |
| freshness | integer | NOT NULL The freshness of the mirror, that is, how up-to-date it is. |
|
| integer | NOT NULL The PackagePublishingPocket. |
||
| public.component.id | component | integer | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
The mirror of a given Distro Release
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distributionmirror.id | distribution_mirror | integer | NOT NULL The distribution mirror. |
| public.distroseries.id | distroseries | integer | NOT NULL The Distribution Release. |
| freshness | integer | NOT NULL The freshness of the mirror, that is, how up-to-date it is. |
|
| integer | NOT NULL | ||
| public.component.id | component | integer | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Records stored when a mirror is probed.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distributionmirror.id | distribution_mirror | integer | NOT NULL The DistributionMirror. |
| public.libraryfilealias.id | log_file | integer | The log file of the probe. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The date and time the probe was performed. |
Stores which distroseries and components a given mirror that includes source packages has.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.mirror.id | mirror | integer | NOT NULL |
| public.distroseries.id | distroseries | integer | NOT NULL A distroseries that this mirror contains. |
| public.component.id | component | integer | NOT NULL What component of the distroseries that this sourcepackage mirror contains. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
A list of regular expressions used to blacklist names.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| regexp | text | UNIQUE NOT NULL A Python regular expression. It will be compiled with the IGNORECASE, UNICODE and VERBOSE flags. The Python search method will be used rather than match, so ^ markers should be used to indicate the start of a string. |
|
| comment | text | An optional comment on why this regexp was entered. It should not be displayed to non-admins and its only purpose is documentation. |
| Name | Constraint |
|---|---|
| valid_regexp | CHECK (valid_regexp(regexp)) |
An access token used by the consumer to act on behalf of one of our users.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.oauthconsumer.id | consumer | integer | NOT NULL The consumer which is going to access the protected resources. |
| public.person.id | person | integer | NOT NULL The person on whose behalf the consumer will access Launchpad. |
| permission | integer | NOT NULL The permission given by that person to the consumer. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date/time in which the token was created. |
|
| date_expires | timestamp without time zone | The date/time in which this token will stop being accepted by Launchpad. |
|
| key | text | UNIQUE NOT NULL This token's unique key. |
|
| secret | text | NOT NULL The secret used by the consumer (together with the token's key) to access Launchpad on behalf of the person. |
|
| public.product.id | product | integer | The product associated with this token. |
| public.project.id | project | integer | The project associated with this token. |
| public.distribution.id | distribution | integer | The distribution associated with this token. |
| public.sourcepackagename.id | sourcepackagename | integer | The sourcepackagename associated with this token. |
| Name | Constraint |
|---|---|
| just_one_context | CHECK ((null_count(ARRAY[product, project, distribution]) >= 2)) |
| sourcepackagename_needs_distro | CHECK (((sourcepackagename IS NULL) OR (distribution IS NOT NULL))) |
Tables referencing this one via Foreign Key Constraints:
oauthaccesstoken__consumer__idx consumer oauthaccesstoken__date_expires__idx date_expires) WHERE (date_expires IS NOT NULL oauthaccesstoken__distribution__sourcepackagename__idx distribution, sourcepackagename) WHERE (distribution IS NOT NULL oauthaccesstoken__person__idx person oauthaccesstoken__product__idx product) WHERE (product IS NOT NULL oauthaccesstoken__project__idx project) WHERE (project IS NOT NULLA third part application that will access Launchpad on behalf of one of our users.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The creation date. |
|
| disabled | boolean | NOT NULL DEFAULT false Is this consumer disabled? |
|
| key | text | UNIQUE NOT NULL The unique key for this consumer. |
|
| secret | text | The secret used by this consumer (together with its key) to identify itself with Launchpad. |
Tables referencing this one via Foreign Key Constraints:
The unique nonce for any request with a given timestamp and access token. This is generated by the consumer.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| request_timestamp | timestamp without time zone | UNIQUE#1 NOT NULL DEFAULT timezone('UTC'::text,
now()) The date and time (as a timestamp) in which the request was made. |
|
| nonce | text | UNIQUE#1 NOT NULL The nonce itself. |
|
| public.oauthaccesstoken.id | access_token | integer | UNIQUE#1 NOT NULL The access token. |
A request token which, once authorized by the user, is exchanged for an access token.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.oauthconsumer.id | consumer | integer | NOT NULL The consumer which is going to access the protected resources. |
| public.person.id | person | integer | The person who authorized this token. |
| permission | integer | The permission given by the person to the consumer. |
|
| date_expires | timestamp without time zone | When the authorization is to expire. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date/time in which the token was created. |
|
| date_reviewed | timestamp without time zone | When the authorization request was authorized or rejected by the person. |
|
| key | text | UNIQUE NOT NULL This token's unique key. |
|
| secret | text | NOT NULL The secret used by the consumer (together with the token's key) to get an access token once the user has authorized its use. |
|
| public.product.id | product | integer | The product associated with this token. |
| public.project.id | project | integer | The project associated with this token. |
| public.distribution.id | distribution | integer | The distribution associated with this token. |
| public.sourcepackagename.id | sourcepackagename | integer | The sourcepackagename associated with this token. |
| Name | Constraint |
|---|---|
| just_one_context | CHECK ((null_count(ARRAY[product, project, distribution]) >= 2)) |
| reviewed_request | CHECK ((((date_reviewed IS NULL) = (person IS NULL)) AND ((date_reviewed IS NULL) = (permission IS NULL)))) |
| sourcepackagename_needs_distro | CHECK (((sourcepackagename IS NULL) OR (distribution IS NOT NULL))) |
Bug tags that have been officially endorced by this product's or distribution's lead
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| tag | text | NOT NULL | |
| public.distribution.id | distribution | integer | |
| public.project.id | project | integer | |
| public.product.id | product | integer |
| Name | Constraint |
|---|---|
| context_required | CHECK (((product IS NOT NULL) OR (distribution IS NOT NULL))) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| server_url | character varying(2047) | PRIMARY KEY | |
| handle | character varying(255) | PRIMARY KEY | |
| secret | bytea | ||
| issued | integer | ||
| lifetime | integer | ||
| assoc_type | character varying(64) |
| Name | Constraint |
|---|---|
| secret_length_constraint | CHECK ((length(secret) <= 128)) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.account.id | account | integer | NOT NULL |
| client_id | text | ||
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| date_expires | timestamp without time zone | NOT NULL | |
| trust_root | text | NOT NULL |
| F-Key | Name | Type | Description |
|---|---|---|---|
| server_url | character varying(2047) | PRIMARY KEY | |
| handle | character varying(255) | PRIMARY KEY | |
| secret | bytea | ||
| issued | integer | ||
| lifetime | integer | ||
| assoc_type | character varying(64) |
| Name | Constraint |
|---|---|
| secret_length_constraint | CHECK ((length(secret) <= 128)) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| server_url | character varying(2047) | PRIMARY KEY | |
| timestamp | integer | PRIMARY KEY | |
| salt | character(40) | PRIMARY KEY |
Nonces for our OpenID consumer.
| F-Key | Name | Type | Description |
|---|---|---|---|
| server_url | character varying(2047) | PRIMARY KEY | |
| timestamp | integer | PRIMARY KEY | |
| salt | character(40) | PRIMARY KEY |
Configuration information for OpenID Relying Parties
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| trust_root | text | NOT NULL The trust root for this RP |
|
| displayname | text | NOT NULL The human readable name for this RP |
|
| description | text | NOT NULL A description of the RP. Should indicate why the RP wants the user to log in |
|
| public.libraryfilealias.id | logo | integer | A reference to the logo for this RP |
| allowed_sreg | text | A comma separated list of fields that can be sent to the RP via openid.sreg. The field names should not have the "openid.sreg." prefix |
|
| creation_rationale | integer | NOT NULL DEFAULT 13 A person creation rationale to use for users who create an account while logging in to this RP |
|
| can_query_any_team | boolean | NOT NULL DEFAULT false This RP can query for membership of any or all teams, including private teams. This setting overrides any other private team query ACLs, and should not be used if more granular options are suitable. |
|
| auto_authorize | boolean | NOT NULL DEFAULT false True if the user authorisation page is skipped by default for this RP. |
The summary of the activity between a person and an RP.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.account.id | account | integer | UNIQUE#1 NOT NULL The account who used the RP. |
| openid_identifier | text | UNIQUE#1 NOT NULL The OpenID identifier used to login. |
|
| trust_root | text | UNIQUE#1 NOT NULL The trust root for the RP |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The creation date of this summary; the first time the person used the RP. |
|
| date_last_used | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date the RP was last used. |
|
| total_logins | integer | NOT NULL DEFAULT 1 The total number of times the RP was used by the person. |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| distribution | integer | UNIQUE#1 NOT NULL | |
| sourcepackagename | integer | UNIQUE#1 NOT NULL | |
| public.person.id | bug_supervisor | integer | NOT NULL |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
PackageCopyRequest: A table that captures the status and the details of an inter-archive package copy operation.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.archive.id | target_archive | integer | NOT NULL The archive to which packages will be copied. |
| public.distroseries.id | target_distroseries | integer | The target distroseries. |
| public.component.id | target_component | integer | The target component. |
| target_pocket | integer | The target pocket. |
|
| copy_binaries | boolean | NOT NULL DEFAULT false | |
| public.archive.id | source_archive | integer | NOT NULL The archive from which packages are to be copied. |
| public.distroseries.id | source_distroseries | integer | The distroseries to which the packages to be copied belong in the source archive. |
| public.component.id | source_component | integer | The component to which the packages to be copied belong in the source archive. |
| source_pocket | integer | The pocket for the packages to be copied. |
|
| public.person.id | requester | integer | NOT NULL The person who requested the archive operation. |
| status | integer | NOT NULL Archive operation status, may be one of: new, in-progress, complete, failed, cancelling, cancelled. |
|
| reason | text | The reason why this copy operation was requested. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) Date of creation for this archive operation. |
|
| date_started | timestamp without time zone | Start date/time of this archive operation. |
|
| date_completed | timestamp without time zone | When did this archive operation conclude? |
This table stores diffs bettwen two scpecific SourcePackageRelease versions.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_requested | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) Instant when the diff was requested. |
|
| public.person.id | requester | integer | NOT NULL The Person responsible for the request. |
| public.sourcepackagerelease.id | from_source | integer | NOT NULL The SourcePackageRelease to diff from. |
| public.sourcepackagerelease.id | to_source | integer | NOT NULL The SourcePackageRelease to diff to. |
| date_fulfilled | timestamp without time zone | Instant when the diff was completed. |
|
| public.libraryfilealias.id | diff_content | integer | LibraryFileAlias containing the th diff results. |
| status | integer | NOT NULL Request status, PENDING(0) when created then goes to COMPLETED(1) or FAILED(2), both terminal status where diff_content and date_fulfilled will contain the results of the request. |
| Name | Constraint |
|---|---|
| distinct_sources | CHECK ((from_source <> to_source)) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distroseries.id | distroseries | integer | NOT NULL |
| public.sourcepackagename.id | sourcepackagename | integer | |
| public.binarypackagename.id | binarypackagename | integer | |
| action | integer | NOT NULL | |
| public.component.id | component | integer | |
| public.section.id | section | integer | |
| priority | integer | ||
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Package sets facilitate the grouping of packages for purposes like the control of upload permissions, et.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) Date and time of creation. |
|
| public.person.id | owner | integer | NOT NULL The Person or team who owns the package set |
| name | text | UNIQUE NOT NULL The name for the package set on hand. |
|
| description | text | NOT NULL The description for the package set on hand. |
| Name | Constraint |
|---|---|
| packageset_name_check | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
packageset__owner__idx ownersets may form a set-subset hierarchy; this table facilitates the definition of these set-subset relationships.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.packageset.id | parent | integer | UNIQUE#1 NOT NULL The package set that is including a subset. |
| public.packageset.id | child | integer | UNIQUE#1 NOT NULL The package set that is being included as a subset. |
This table associates package sets and source package names.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.packageset.id | packageset | integer | UNIQUE#1 NOT NULL The associated package set. |
| public.sourcepackagename.id | sourcepackagename | integer | UNIQUE#1 NOT NULL The associated source package name. |
An upload. This table stores information pertaining to uploads to a given DistroSeries/Archive.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| status | integer | NOT NULL This is an integer field containing the current status of the upload. Possible values are given by the UploadStatus class in dbschema.py |
|
| public.distroseries.id | distroseries | integer | NOT NULL This integer field refers to the DistroSeries to which this upload is targeted |
| integer | NOT NULL This is the pocket the upload is targeted at. |
||
| public.libraryfilealias.id | changesfile | integer | The changes file associated with this upload. It is null for records refering to a delayed-copy. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.gpgkey.id | signing_key | integer | |
| public.archive.id | archive | integer | NOT NULL The archive to which this upload is targetted. |
Tables referencing this one via Foreign Key Constraints:
packageupload__changesfile__idx changesfile packageupload__distroseries__key distroseries packageupload__distroseries__status__idx distroseries, status packageupload__signing_key__idx signing_keyAn upload binary build. This table stores information pertaining to the builds in a package upload.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.packageupload.id | packageupload | integer | UNIQUE#1 NOT NULL This integer field refers to the PackageUpload row that this source belongs to. |
| public.build.id | build | integer | UNIQUE#1 NOT NULL This integer field refers to the Build record related to this upload. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
An uploaded custom format file. This table stores information pertaining to the custom upload formats in a package upload.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.packageupload.id | packageupload | integer | NOT NULL The PackageUpload row this refers to. |
| customformat | integer | NOT NULL The format of this particular custom uploaded file. |
|
| public.libraryfilealias.id | libraryfilealias | integer | NOT NULL The actual file as a librarian alias. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Link between an upload and a source package. This table stores information pertaining to the source files in a package upload.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.packageupload.id | packageupload | integer | UNIQUE NOT NULL This integer field refers to the PackageUpload row that this source belongs to. |
| public.sourcepackagerelease.id | sourcepackagerelease | integer | NOT NULL This integer field refers to the SourcePackageRelease record related to this upload. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
DO NOT JOIN THROUGH THIS TABLE. This is a set of information linking upstream product series (branches) to distro packages, but it's not planned or likely to be complete, in the sense that we do not attempt to have information for every branch in every derivative distro managed in Launchpad. So don't join through this table to get from product to source package, or vice versa. Rather, use the ProductSeries.sourcepackages attribute, or the SourcePackage.productseries attribute. You may need to create a SourcePackage with a given sourcepackagename and distroseries, then use its .productrelease attribute. The code behind those methods does more than just join through the tables, it is also smart enough to look at related distro's and parent distroseries, and at Ubuntu in particular.
| F-Key | Name | Type | Description |
|---|---|---|---|
| packaging | integer | NOT NULL A dbschema Enum (PackagingType) describing the way the upstream productseries has been packaged. Generally it will be of type PRIME, meaning that the upstream productseries is the primary substance of the package, but it might also be INCLUDES, if the productseries has been included as a statically linked library, for example. This allows us to say that a given Source Package INCLUDES libneon but is a PRIME package of tla, for example. By INCLUDES we mean that the code is actually lumped into the package as ancilliary support material, rather than simply depending on a separate packaging of that code. |
|
| id | serial | PRIMARY KEY | |
| public.sourcepackagename.id | sourcepackagename | integer | UNIQUE#1 The source package name for the source package that includes the upstream productseries described in this Packaging record. There is no requirement that such a sourcepackage actually be published in the distro. |
| public.distroseries.id | distroseries | integer | UNIQUE#1 The distroseries in which the productseries has been packaged. |
| public.productseries.id | productseries | integer | UNIQUE#1 NOT NULL The upstream product series that has been packaged in this distroseries sourcepackage. |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | owner | integer | This is not the "owner" in the sense of giving the person any special privileges to edit the Packaging record, it is simply a record of who told us about this packaging relationship. Note that we do not keep a history of these, so if someone sets it correctly, then someone else sets it incorrectly, we lose the first setting. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
A parsed apache log file for librarian.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| first_line | text | NOT NULL The first line of this log file, smashed to ASCII. This uniquely identifies the log file, even if its filename is changed by log rotation or archival. |
|
| bytes_read | integer | NOT NULL The number of bytes from this log file that have been parsed. |
|
| date_last_parsed | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
A row represents a person if teamowner is NULL, and represents a team if teamowner is set.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| displayname | text | NOT NULL Person or group's name as it should be rendered to screen |
|
| public.person.id | teamowner | integer | id of the team owner. Team owners will have authority to add or remove people from the team. |
| teamdescription | text | Informative description of the team. Format and restrictions are as yet undefined. |
|
| name | text | UNIQUE NOT NULL Short mneumonic name uniquely identifying this person or team. Useful for url traversal or in places where we need to unambiguously refer to a person or team (as displayname is not unique). |
|
| public.language.id | language | integer | Preferred language for this person (unset for teams). UI should be displayed in this language wherever possible. |
| fti | ts2.tsvector | ||
| defaultmembershipperiod | integer | ||
| defaultrenewalperiod | integer | ||
| subscriptionpolicy | integer | NOT NULL DEFAULT 1 The policy for new members to join this team. |
|
| public.person.id | merged | integer | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| addressline1 | text | ||
| addressline2 | text | ||
| organization | text | ||
| city | text | ||
| province | text | ||
| public.country.id | country | integer | |
| postcode | text | ||
| phone | text | ||
| homepage_content | text | A home page for this person in the Launchpad. In short, this is like a personal wiki page. The person will get to edit their own page, and it will be published on /people/foo/. Note that this is in text format, and will migrate to being in Moin format as a sort of mini-wiki-homepage. |
|
| public.libraryfilealias.id | icon | integer | The library file alias to a small image to be used as an icon whenever we are referring to that person. |
| public.libraryfilealias.id | mugshot | integer | The library file alias of a hackermugshot image to display as the "face" of a person, on their home page. |
| hide_email_addresses | boolean | NOT NULL DEFAULT false | |
| creation_rationale | integer | The rationale for the creation of this person -- a dbschema value. |
|
| creation_comment | text | A text comment for the creation of this person. |
|
| public.person.id | registrant | integer | The user who created this profile. |
| public.libraryfilealias.id | logo | integer | The library file alias of a smaller version of this person's mugshot. |
| renewal_policy | integer | NOT NULL DEFAULT 10 The policy for membership renewal on this team. |
|
| personal_standing | integer | NOT NULL The standing of the person, which indicates (for now, just) whether the person can post to a mailing list without requiring first post moderation. Values are documented in dbschema.PersonalStanding. |
|
| personal_standing_reason | text | The reason a person's standing has changed. |
|
| mail_resumption_date | date | A NULL resumption date or a date in the past indicates that there is no vacation in effect. Vacations are granular to the day, so a datetime is not necessary. |
|
| mailing_list_auto_subscribe_policy | integer | NOT NULL DEFAULT 1 The auto-subscription policy for the person, i.e. whether and how the user is automatically subscribed to mailing lists for teams they join. Values are described in dbschema.MailingListAutoSubscribePolicy. |
|
| mailing_list_receive_duplicates | boolean | NOT NULL DEFAULT true True means the user wants to receive list copies of messages on which they are explicitly named as a recipient. |
|
| visibility | integer | NOT NULL DEFAULT 1 person.PersonVisibility enumeration which can be set to Public, Public with Private Membership, or Private. |
|
| verbose_bugnotifications | boolean | NOT NULL DEFAULT false If true, all bugnotifications sent to this Person will include the bug description. |
|
| account | integer | UNIQUE The Account linked to this Person, if there is one. |
| Name | Constraint |
|---|---|
| creation_rationale_not_null_for_people | CHECK (((creation_rationale IS NULL) = (teamowner IS NOT NULL))) |
| no_loops | CHECK ((id <> teamowner)) |
| non_empty_displayname | CHECK ((btrim(displayname) <> ''::text)) |
| people_have_no_emblems | CHECK (((icon IS NULL) OR (teamowner IS NOT NULL))) |
| sane_defaultrenewalperiod | CHECK (CASE WHEN (teamowner IS NULL) THEN (defaultrenewalperiod IS NULL) WHEN (renewal_policy = ANY (ARRAY[20, 30])) THEN ((defaultrenewalperiod IS NOT NULL) AND (defaultrenewalperiod > 0)) ELSE ((defaultrenewalperiod IS NULL) OR (defaultrenewalperiod > 0)) END) |
| teams_have_no_account | CHECK (((account IS NULL) OR (teamowner IS NULL))) |
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
person__icon__idx icon) WHERE (icon IS NOT NULL person__logo__idx logo) WHERE (logo IS NOT NULL person__merged__idx merged) WHERE (merged IS NOT NULL person__mugshot__idx mugshot) WHERE (mugshot IS NOT NULL person__teamowner__idx teamowner) WHERE (teamowner IS NOT NULL person_datecreated_idx datecreated person_fti fti person_sorting_idx person_sort_key(displayname, name)PersonLanguage: This table stores the preferred languages that a Person has, it's used in Rosetta to select the languages that should be showed to be translated.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | UNIQUE#1 NOT NULL This field is a reference to a Person object that has this preference. |
| public.language.id | language | integer | UNIQUE#1 NOT NULL This field is a reference to a Language object that says that the Person associated to this row knows how to translate/understand this language. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
The geographical coordinates and time zone for a person.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.person.id | person | integer | UNIQUE NOT NULL |
| latitude | double precision | The latitude this person has given for their default location. |
|
| longitude | double precision | The longitude this person has given for their default location. |
|
| time_zone | text | The name of the time zone this person prefers (if unset, UTC is used). UI should display dates and times in this time zone wherever possible. |
|
| public.person.id | last_modified_by | integer | NOT NULL The person who last updated this record. We allow people to provide location and time zone information for other users, when those users have not specified their own location. This allows people to garden the location information for their teams, for example, like a wiki. |
| date_last_modified | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date this record was last modified. |
|
| visible | boolean | DEFAULT true Should this person's location and time zone be visible to others? |
|
| locked | boolean | DEFAULT false Whether or not this record can be modified by someone other than the person himself? |
| Name | Constraint |
|---|---|
| latitude_and_longitude_together | CHECK (((latitude IS NULL) = (longitude IS NULL))) |
Notifications to be sent that are related to edits and changes of the details of a specific person or team. Note that these are not keyed against the "person who will be notified", these are notifications "about a person". We use this table to queue up notifications that can then be sent asyncronously - when one user edits information about another person (like the PersonLocation) we want to notify the person concerned that their details have been modified but we do not want to do this during the handling of the form submission. So we store the reminder to notify here, and send it later in a batch. This is modelled on the pattern of BugNotification.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | NOT NULL The Person who has been edited or modified. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| date_emailed | timestamp without time zone | When this notification was emailed to the relevant people. |
|
| body | text | NOT NULL The textual body of the notification to be sent. |
|
| subject | text | NOT NULL The subject of the mail to be sent. |
A cache of the names of our "Pillar's" (distribution, product, project) to ensure uniqueness in this shared namespace. This is a materialized view maintained by database triggers.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL | |
| public.product.id | product | integer | |
| public.project.id | project | integer | |
| public.distribution.id | distribution | integer | |
| active | boolean | NOT NULL DEFAULT true | |
| public.pillarname.id | alias_for | integer | An alias for another pillarname. Rows with this column set are not maintained by triggers. |
| Name | Constraint |
|---|---|
| only_one_target | CHECK ((null_count(ARRAY[product, project, distribution, alias_for]) = 3)) |
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
pillarname__alias_for__idx alias_for) WHERE (alias_for IS NOT NULLPocketChroots: Which chroot belongs to which pocket of which distroarchseries. Any given pocket of any given distroarchseries needs a specific chroot in order to be built. This table links it all together.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distroarchseries.id | distroarchseries | integer | UNIQUE#1 Which distroarchseries this chroot applies to. |
| integer | UNIQUE#1 NOT NULL Which pocket of the distroarchseries this chroot applies to. Valid values are specified in dbschema.PackagePublishingPocket |
||
| public.libraryfilealias.id | chroot | integer | The chroot used by the pocket of the distroarchseries. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.potemplate.id | potemplate | integer | NOT NULL |
| public.pomsgid.id | pomsgid | integer | |
| public.language.id | language | integer | |
| public.potranslation.id | potranslation | integer | |
| commenttext | text | NOT NULL | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | person | integer |
A request from a user that a PO template or a PO file be exported asynchronously.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | NOT NULL The person who made the request. |
| public.potemplate.id | potemplate | integer | NOT NULL The PO template being requested. |
| public.pofile.id | pofile | integer | The PO file being requested, or NULL. |
| format | integer | NOT NULL The format the user would like the export to be in. See the RosettaFileFormat DB schema for possible values. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
This table stores a PO file for a given PO template.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.potemplate.id | potemplate | integer | NOT NULL |
| public.language.id | language | integer | NOT NULL |
| description | text | ||
| topcomment | text | ||
| header | text | ||
| fuzzyheader | boolean | NOT NULL | |
| public.person.id | lasttranslator | integer | |
| currentcount | integer | NOT NULL | |
| updatescount | integer | NOT NULL | |
| rosettacount | integer | NOT NULL | |
| lastparsed | timestamp without time zone | ||
| public.person.id | owner | integer | NOT NULL |
| variant | text | ||
| path | text | NOT NULL The path (included the filename) inside the tree from where the content was imported. |
|
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.sourcepackagename.id | from_sourcepackagename | integer | The sourcepackagename from where the last .po file came (only if it's different from POFile.potemplate.sourcepackagename) |
| unreviewed_count | integer | NOT NULL Number of POTMsgSets with new, unreviewed TranslationMessages for this POFile. |
|
| date_changed | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
| Name | Constraint |
|---|---|
| valid_variant | CHECK ((variant <> ''::text)) |
Tables referencing this one via Foreign Key Constraints:
pofile_datecreated_idx datecreated pofile_language_idx language pofile_lasttranslator_idx lasttranslator pofile_owner_idx owner pofile_variant_idx variantA materialized view caching who has translated what pofile.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | UNIQUE#1 NOT NULL The person who submitted the translation. |
| public.pofile.id | pofile | integer | UNIQUE#1 NOT NULL The pofile the translation was submitted for. |
| public.translationmessage.id | latest_message | integer | NOT NULL Latest translation message added to the translation file. |
| date_last_touched | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) When was added latest translation message. |
The polls belonging to teams.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | team | integer | UNIQUE#1 NOT NULL The team this poll belongs to |
| name | text | UNIQUE#1 NOT NULL The unique name of this poll. |
|
| title | text | NOT NULL The title of this poll. |
|
| dateopens | timestamp without time zone | NOT NULL The date and time when this poll opens. |
|
| datecloses | timestamp without time zone | NOT NULL The date and time when this poll closes. |
|
| proposition | text | NOT NULL The proposition that is going to be voted. |
|
| type | integer | NOT NULL The type of this poll (Simple, Preferential, etc). |
|
| allowspoilt | boolean | NOT NULL DEFAULT false If people can spoil their votes. |
|
| secrecy | integer | NOT NULL If people votes are SECRET (no one can see), ADMIN (team administrators can see) or PUBLIC (everyone can see). |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
| Name | Constraint |
|---|---|
| sane_dates | CHECK ((dateopens < datecloses)) |
Tables referencing this one via Foreign Key Constraints:
The options belonging to polls.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY UNIQUE#2 | |
| public.poll.id | poll | integer | UNIQUE#1 UNIQUE#2 NOT NULL The poll this options belongs to. |
| name | text | UNIQUE#1 NOT NULL The name of this option. |
|
| title | text | NOT NULL A short title for this option. |
|
| active | boolean | NOT NULL DEFAULT true If TRUE, people will be able to vote on this option. Otherwise they don't. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Tables referencing this one via Foreign Key Constraints:
polloption_poll_idx poll| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| msgid | text | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | UNIQUE#1 NOT NULL |
| public.potemplate.id | potemplate | integer | UNIQUE#1 NOT NULL |
| public.language.id | language | integer | UNIQUE#1 |
| notificationinterval | interval | ||
| lastnotified | timestamp without time zone | ||
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
This table stores a pot file for a given product.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| priority | integer | NOT NULL | |
| description | text | ||
| copyright | text | ||
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| path | text | NOT NULL The path to the .pot source file inside the tarball tree, including the filename. |
|
| iscurrent | boolean | NOT NULL | |
| messagecount | integer | NOT NULL | |
| public.person.id | owner | integer | NOT NULL |
| public.sourcepackagename.id | sourcepackagename | integer | A reference to a sourcepackage name from where this POTemplate comes. |
| public.distroseries.id | distroseries | integer | A reference to the distribution from where this POTemplate comes. |
| sourcepackageversion | text | The sourcepackage version string from where this potemplate was imported last time with our buildd <-> Rosetta gateway. |
|
| header | text | NOT NULL The header of a .pot file when we import it. Most important info from it is POT-Creation-Date and custom headers. |
|
| public.binarypackagename.id | binarypackagename | integer | |
| languagepack | boolean | NOT NULL DEFAULT false | |
| public.productseries.id | productseries | integer | A reference to a ProductSeries from where this POTemplate comes. |
| public.sourcepackagename.id | from_sourcepackagename | integer | The sourcepackagename from where the last .pot file came (only if it's different from POTemplate.sourcepackagename) |
| date_last_updated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.libraryfilealias.id | source_file | integer | Reference to Librarian file storing the last uploaded template file. |
| source_file_format | integer | NOT NULL DEFAULT 1 File format for the Librarian file referenced in "source_file" column. |
|
| name | text | NOT NULL The name of the POTemplate set. It must be unique |
|
| translation_domain | text | NOT NULL The translation domain for this POTemplate |
| Name | Constraint |
|---|---|
| potemplate_valid_name | CHECK (valid_name(name)) |
| valid_from_sourcepackagename | CHECK (((sourcepackagename IS NOT NULL) OR (from_sourcepackagename IS NULL))) |
| valid_link | CHECK ((((productseries IS NULL) <> (distroseries IS NULL)) AND ((distroseries IS NULL) = (sourcepackagename IS NULL)))) |
Tables referencing this one via Foreign Key Constraints:
potemplate__date_last_updated__idx date_last_updated potemplate__name__idx name potemplate__source_file__idx source_file) WHERE (source_file IS NOT NULL potemplate_languagepack_idx languagepack potemplate_owner_idx owner| F-Key | Name | Type | Description |
|---|---|---|---|
| id | text | ||
| productseries | integer | ||
| sourcepackagename | integer | ||
| distroseries | integer | ||
| potemplate | integer | ||
| template_header | text | ||
| languagepack | boolean | ||
| sequence | integer | ||
| potmsgset | integer | ||
| comment | text | ||
| source_comment | text | ||
| file_references | text | ||
| flags_comment | text | ||
| context | text | ||
| msgid_singular | text | ||
| msgid_plural | text |
SELECT COALESCE
(
(potmsgset.id)::text
,'X'::text
) AS id
, potemplate.productseries
, potemplate.sourcepackagename
, potemplate.distroseries
, potemplate.id AS potemplate
, potemplate.header AS template_header
, potemplate.languagepack
, translationtemplateitem.sequence
, potmsgset.id AS potmsgset
, potmsgset.commenttext AS comment
, potmsgset.sourcecomment AS source_comment
, potmsgset.filereferences AS file_references
, potmsgset.flagscomment AS flags_comment
, potmsgset.context
, msgid_singular.msgid AS msgid_singular
, msgid_plural.msgid AS msgid_plural
FROM (
(
(
(potmsgset
JOIN translationtemplateitem
ON (
(translationtemplateitem.potmsgset = potmsgset.id)
)
)
JOIN potemplate
ON (
(potemplate.id = translationtemplateitem.potemplate)
)
)
LEFT JOIN pomsgid msgid_singular
ON (
(potmsgset.msgid_singular = msgid_singular.id)
)
)
LEFT JOIN pomsgid msgid_plural
ON (
(potmsgset.msgid_plural = msgid_plural.id)
)
);
This table is stores a collection of msgids without their translations and all kind of information associated to that set of messages that could be found in a potemplate file.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.pomsgid.id | msgid_singular | integer | NOT NULL The singular msgid for this message. |
| sequence | integer | The position of this message set inside the potemplate. |
|
| public.potemplate.id | potemplate | integer | The potemplate where this message set is stored. |
| commenttext | text | The comment text that is associated to this message set. |
|
| filereferences | text | The list of files and their line number where this message set was extracted from. |
|
| sourcecomment | text | The comment that was extracted from the source code. |
|
| flagscomment | text | The flags associated with this set (like c-format). |
|
| context | text | Context uniquely defining a message when there are messages with same primemsgids. |
|
| public.pomsgid.id | msgid_plural | integer | The plural msgid for this message. |
Tables referencing this one via Foreign Key Constraints:
potmsgset__context__msgid_singular__msgid_plural__idx context, msgid_singular, msgid_plural) WHERE ((context IS NOT NULL) AND (msgid_plural IS NOT NULL) potmsgset__context__msgid_singular__no_msgid_plural__idx context, msgid_singular) WHERE ((context IS NOT NULL) AND (msgid_plural IS NULL) potmsgset__no_context__msgid_singular__msgid_plural__idx msgid_singular, msgid_plural) WHERE ((context IS NULL) AND (msgid_plural IS NOT NULL) potmsgset__no_context__msgid_singular__no_msgid_plural__idx msgid_singular) WHERE ((context IS NULL) AND (msgid_plural IS NULL) potmsgset_primemsgid_idx msgid_singular potmsgset_sequence_idx sequence| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| translation | text | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
Contains information about preview diffs, without duplicating information with BranchMergeProposal.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| source_revision_id | text | NOT NULL The source branch revision_id used to generate this diff. |
|
| target_revision_id | text | NOT NULL The target branch revision_id used to generate this diff. |
|
| dependent_revision_id | text | The dependant branch revision_id used to generate this diff. |
|
| public.diff.id | diff | integer | NOT NULL The last Diff generated for this PreviewDiff. |
| conflicts | text | The text description of any conflicts present. |
Tables referencing this one via Foreign Key Constraints:
previewdiff__diff__idx diffA single processor for which code might be compiled. For example, i386, P2, P3, P4, Itanium1, Itanium2... each processor belongs to a ProcessorFamily, and it might be that a package is compiled several times for a given Family, with different optimisation settings for each processor.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.processorfamily.id | family | integer | NOT NULL The ProcessorFamily for this Processor. |
| name | text | UNIQUE NOT NULL The name of this processor, for example, i386, Pentium, P2, P3, P4, Itanium, Itanium2, K7, Athlon, Opteron... it should be short and unique. |
|
| title | text | NOT NULL | |
| description | text | NOT NULL |
Tables referencing this one via Foreign Key Constraints:
An architecture, that might consist of several actual processors. Different distributions call these architectures different things, so we have an "architecturetag" in DistroArchSeries that might be different to the architecture's name.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL The name of the architecture. This is a short unix-style name such as i386 or amd64 |
|
| title | text | NOT NULL A title for the architecture. For example "Intel i386 Compatible". |
|
| description | text | NOT NULL A description for this processor family. It might include any gotchas such as the fact that i386 does not necessarily mean that code would run on a 386... Ubuntu for example requires a 486. |
Tables referencing this one via Foreign Key Constraints:
Product: a DOAP Product. This table stores core information about an open source product. In Launchpad, anything that can be shipped as a tarball would be a product, and in some cases there might be products for things that never actually ship, depending on the project. For example, most projects will have a 'website' product, because that allows you to file a Malone bug against the project website. Note that these are not actual product releases, which are stored in the ProductRelease table.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.project.id | project | integer | Every Product belongs to one and only one Project, which is referenced in this column. |
| public.person.id | owner | integer | NOT NULL The Product owner would typically be the person who created this product in Launchpad. But we will encourage the upstream maintainer of a product to become the owner in Launchpad. The Product owner can edit any aspect of the Product, as well as appointing people to specific roles with regard to the Product. Also, the owner can add a new ProductRelease and also edit Rosetta POTemplates associated with this product. |
| name | text | UNIQUE NOT NULL | |
| displayname | text | NOT NULL | |
| title | text | NOT NULL | |
| summary | text | NOT NULL A brief summary of the product. This will be displayed in bold at the top of the product page, above the description. |
|
| description | text | A detailed description of the product, highlighting primary features of the product that may be of interest to end-users. The description may also include links and other references to useful information on the web about this product. The description will be displayed on the product page, below the product summary. |
|
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| homepageurl | text | ||
| screenshotsurl | text | ||
| wikiurl | text | ||
| listurl | text | This is the URL where information about a mailing list for this Product can be found. The URL might point at a web archive or at the page where one can subscribe to the mailing list. |
|
| programminglang | text | This field records, in plain text, the name of any significant programming languages used in this product. There are no rules, conventions or restrictions on this field at present, other than basic sanity. Examples might be "Python", "Python, C" and "Java". |
|
| downloadurl | text | The download URL for a Product should be the best place to download that product, typically off the relevant Project web site. This should not point at the actual file, but at a web page with download information. |
|
| lastdoap | text | This column stores a cached copy of the last DOAP description we saw for this product. See the Project.lastdoap field for more info. |
|
| sourceforgeproject | text | The SourceForge project name for this product. This is not unique as SourceForge doesn't use the same project/product structure as DOAP. |
|
| freshmeatproject | text | The FreshMeat project name for this product. This is not unique as FreshMeat does not have the same project/product structure as DOAP |
|
| reviewed | boolean | NOT NULL DEFAULT false Whether or not someone at Canonical has reviewed this product. |
|
| active | boolean | NOT NULL DEFAULT true Whether or not this product should be considered active. |
|
| fti | ts2.tsvector | ||
| autoupdate | boolean | NOT NULL DEFAULT false | |
| public.translationgroup.id | translationgroup | integer | The TranslationGroup that is responsible for translations for this product. Note that the Product may be part of a Project which also has a TranslationGroup, in which case the translators from both the product and project translation group have permission to edit the translations of this product. |
| translationpermission | integer | NOT NULL DEFAULT 1 The level of openness of this product's translation process. The enum lists different approaches to translation, from the very open (anybody can edit any translation in any language) to the completely closed (only designated translators can make any changes at all). |
|
| official_rosetta | boolean | NOT NULL DEFAULT false Whether or not this product upstream uses Rosetta for its official translation team and coordination. This is a useful indicator in terms of whether translations in Rosetta for this upstream will quickly move upstream. |
|
| official_malone | boolean | NOT NULL DEFAULT false Whether or not this product upstream uses Malone for an official bug tracker. This is useful to help indicate whether or not people are likely to pick up on bugs registered in Malone. |
|
| public.person.id | bug_supervisor | integer | Person who is responsible for managing bugs on this product. |
| public.person.id | security_contact | integer | The person or team who handles security-related issues in the product. |
| public.person.id | driver | integer | This is a driver for the overall product. This driver will be able to approve nominations of bugs and specs to any series in the product, including backporting to old stable series. You want the smallest group of "overall drivers" here, because you can add specific drivers to each series individually. |
| public.bugtracker.id | bugtracker | integer | |
| public.productseries.id | development_focus | integer | The product series that is the current focus of development. |
| homepage_content | text | A home page for this product in the Launchpad. |
|
| public.libraryfilealias.id | icon | integer | The library file alias to a small image to be used as an icon whenever we are referring to a product. |
| public.libraryfilealias.id | mugshot | integer | The library file alias of a mugshot image to display as the branding of a product, on its home page. |
| public.libraryfilealias.id | logo | integer | The library file alias of a smaller version of this product's mugshot. |
| official_answers | boolean | NOT NULL DEFAULT false Whether or not this product upstream uses Answers officialy. This is useful to help indicate whether or not that a question will receive an answer. |
|
| private_bugs | boolean | NOT NULL DEFAULT false Indicates whether bugs filed in this product are automatically marked as private. |
|
| private_specs | boolean | NOT NULL DEFAULT false Indicates whether specs filed in this product are automatically marked as private. |
|
| license_info | text | Additional information about licenses that are not included in the License enumeration. |
|
| official_blueprints | boolean | NOT NULL DEFAULT false Whether or not this product upstream uses Blueprints officially. This is useful to help indicate whether or not the upstream project will be actively watching the blueprints in Launchpad. |
|
| enable_bug_expiration | boolean | NOT NULL DEFAULT false Indicates whether automatic bug expiration is enabled. |
|
| bug_reporting_guidelines | text | Guidelines to the end user for reporting bugs on this product. |
|
| reviewer_whiteboard | text | A whiteboard for Launchpad admins, registry experts and the project owners to capture the state of current issues with the project. |
|
| official_codehosting | boolean | NOT NULL DEFAULT false Whether or not this product upstream uses codehosting officially. |
|
| license_approved | boolean | NOT NULL DEFAULT false The Other/Open Source license has been approved by an administrator. |
|
| public.person.id | registrant | integer | NOT NULL The Product registrant is the Person who created the product in Launchpad. It is set at creation and is never changed thereafter. |
| remote_product | text | The ID of this product on its remote bug tracker. |
| Name | Constraint |
|---|---|
| only_launchpad_has_expiration | CHECK (((enable_bug_expiration IS FALSE) OR (official_malone IS TRUE))) |
| private_bugs_need_contact | CHECK (((private_bugs IS FALSE) OR (bug_supervisor IS NOT NULL))) |
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
product__bug_supervisor__idx bug_supervisor) WHERE (bug_supervisor IS NOT NULL product__driver__idx driver) WHERE (driver IS NOT NULL product__icon__idx icon) WHERE (icon IS NOT NULL product__logo__idx logo) WHERE (logo IS NOT NULL product__mugshot__idx mugshot) WHERE (mugshot IS NOT NULL product__registrant__idx registrant product__security_contact__idx security_contact) WHERE (security_contact IS NOT NULL product_active_idx active product_bugcontact_idx bug_supervisor product_fti fti product_owner_idx owner product_project_idx project product_translationgroup_idx translationgroupThis table records a simple link between a bounty and a product. This bounty will be listed on the product web page, and the product will be mentioned on the bounty web page.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bounty.id | bounty | integer | UNIQUE#1 NOT NULL |
| public.product.id | product | integer | UNIQUE#1 NOT NULL |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.product.id | product | integer | NOT NULL |
| anonroot | text | NOT NULL | |
| module | text | NOT NULL | |
| weburl | text | ||
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
The licenses that cover the software for a product.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.product.id | product | integer | UNIQUE#1 NOT NULL Foreign key to the product that has licenses associated with it. |
| license | integer | UNIQUE#1 NOT NULL An integer referencing a value in the License enumeration in product.py |
A Product Release. This is table stores information about a specific 'upstream' software release, like Apache 2.0.49 or Evolution 1.5.4.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datereleased | timestamp without time zone | NOT NULL The date when this version of the product was released. |
|
| release_notes | text | Description of changes in this product release. |
|
| changelog | text | Detailed description of changes in this product release. |
|
| public.person.id | owner | integer | NOT NULL The person who created this product release. |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The timestamp when this product release was created. |
|
| public.milestone.id | milestone | integer | UNIQUE NOT NULL The milestone for this product release. This is scheduled to become a NOT NULL column, so every product release will be linked to a unique milestone. |
Tables referencing this one via Foreign Key Constraints:
productrelease_datecreated_idx datecreated productrelease_owner_idx ownerLinks a ProductRelease to one or more files in the Librarian.
| F-Key | Name | Type | Description |
|---|---|---|---|
| public.productrelease.id | productrelease | integer | NOT NULL This is the product release this file is associated with |
| public.libraryfilealias.id | libraryfile | integer | NOT NULL This is the librarian entry |
| filetype | integer | NOT NULL An enum of what kind of file this is. Code tarballs are marked for special treatment (importing into bzr) |
|
| id | serial | PRIMARY KEY | |
| description | text | A description of what the file contains |
|
| public.person.id | uploader | integer | NOT NULL The person who uploaded this file. |
| date_uploaded | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date this file was uploaded. |
|
| fti | ts2.tsvector | ||
| public.libraryfilealias.id | signature | integer | This is the signature of the librarian entry as uploaded by the user. |
A ProductSeries is a set of product releases that are related to a specific version of the product. Typically, each major release of the product starts a new ProductSeries. These often map to a branch in the revision control system of the project, such as "2_0_STABLE". A few conventional Series names are "head" for releases of the HEAD branch, "1.0" for releases with version numbers like "1.0.0" and "1.0.1". Each product has at least one ProductSeries
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY UNIQUE#3 | |
| public.product.id | product | integer | UNIQUE#1 UNIQUE#3 NOT NULL |
| name | text | UNIQUE#1 NOT NULL The name of the ProductSeries is like a unix name, it should not contain any spaces and should start with a letter or number. Good examples are "2.0", "3.0", "head" and "development". |
|
| summary | text | NOT NULL A summary of this Product Series. A good example would include the date the series was initiated and whether this is the current recommended series for people to use. The summary is usually displayed at the top of the page, in bold, just beneath the title and above the description, if there is a description field. |
|
| importstatus | integer | A status flag which gives the state of our efforts to import the upstream code from its revision control system and publish that in the baz revision control system. The allowed values are documented in ImportStatus. |
|
| datelastsynced | timestamp without time zone | The timestamp when we last successfully completed a production sync of this upstream repository. |
|
| syncinterval | interval | ||
| rcstype | integer | The revision control system used by upstream for this product series. The value is defined in dbschema.RevisionControlSystems. If NULL, then there should be no CVS or SVN information attached to this productseries, otherwise the relevant fields for CVS or SVN etc should be filled out. |
|
| cvsroot | text | UNIQUE#2 The CVS root where this productseries hosts its code. Only used if rcstype is CVS. |
|
| cvsmodule | text | UNIQUE#2 The CVS branch that contains the upstream code for this productseries. Only used if rcstype is CVS. |
|
| cvsbranch | text | UNIQUE#2 | |
| cvstarfileurl | text | The URL of a tarfile of the CVS repository for this productseries. This is an optimisation of the CVS import process - instead of hitting the server to pass us every set of changes in history, we can sometimes arrange to be given a tarfile of the CVS repository and then process it all locally. Once imported, we switch back to using the CVS server for ongoing syncronization. Only used if rcstype is CVS. |
|
| svnrepository | text | UNIQUE The URL of the SVN branch where the upstream productseries code can be found. This single URL is the equivalent of the cvsroot, cvsmodule and cvsbranch for CVS. Only used if rcstype is SVN. |
|
| releasefileglob | text | A fileglob that lets us see which URLs are potentially new upstream tarball releases. For example: http://ftp.gnu.org/gnu/libtool/libtool-1.5.*.gz. |
|
| releaseverstyle | integer | An enum giving the style of this product series release version numbering system. The options are documented in dbschema.UpstreamReleaseVersionStyle. Most applications use Gnu style numbering, but there are other alternatives. |
|
| dateautotested | timestamp without time zone | This upstream revision control system target has passed automatic testing. It can probably be moved towards production sync status. This date is the timestamp when it passed the autotester. The autotester allows us to find the low hanging fruit that is easily brought into the bazaar import system by highlighting repositories which had no apparent difficulty in being imported. |
|
| dateprocessapproved | timestamp without time zone | The timestamp when this upstream import was certified for processing. Processing means it has passed autotesting, and is being moved towards production syncing. If the sync goes well, it will be approved for sync and then be fully in production. |
|
| datesyncapproved | timestamp without time zone | The timestamp when this upstream import was certified for ongoing syncronisation. |
|
| datestarted | timestamp without time zone | The timestamp when we last initiated an import test or sync of this upstream repository. |
|
| datefinished | timestamp without time zone | The timestamp when we last completed an import test or sync of this upstream repository. |
|
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | driver | integer | This is a person or team who can approve spes and bugs for implementation or fixing in this specific series. Note that the product drivers and project drivers can also do this for any series in the product or project, so use this only for the specific team responsible for this specific series. |
| public.person.id | owner | integer | NOT NULL |
| date_published_sync | timestamp without time zone | The saved value of datelastsynced from the last time it was older than the corresponding branch's last_mirrored timestamp. The timestamp currently published import branch is either datelastsynced or datepublishedsync. |
|
| status | integer | NOT NULL DEFAULT 2 The current status of this productseries. |
|
| translations_autoimport_mode | integer | NOT NULL DEFAULT 1 Level of translations imports from codehosting branch: None, templates only, templates and translations. See TranslationsBranchImportMode. |
|
| public.branch.id | branch | integer | The branch for this product series. |
| public.branch.id | translations_branch | integer | Branch to push translations updates to. |
| Name | Constraint |
|---|---|
| complete_cvs | CHECK ((((cvsroot IS NULL) = (cvsmodule IS NULL)) AND ((cvsroot IS NULL) = (cvsbranch IS NULL)))) |
| no_empty_strings | CHECK (((((cvsroot <> ''::text) AND (cvsmodule <> ''::text)) AND (cvsbranch <> ''::text)) AND (svnrepository <> ''::text))) |
| valid_importseries | CHECK (((importstatus IS NULL) OR (rcstype IS NOT NULL))) |
| valid_name | CHECK (valid_name(name)) |
| valid_releasefileglob | CHECK (valid_absolute_url(releasefileglob)) |
Tables referencing this one via Foreign Key Constraints:
productseries__branch__idx branch) WHERE (branch IS NOT NULL productseries__translations_branch__idx translations_branch productseries_datecreated_idx datecreatedA record of which ProductSeries' import data a CodeImport was constructed from.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.productseries.id | productseries | integer | UNIQUE NOT NULL The source ProductSeries. |
| public.codeimport.id | codeimport | integer | UNIQUE NOT NULL The CodeImport that was constructed from the ProductSeries. |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.product.id | product | integer | NOT NULL |
| locationurl | text | NOT NULL | |
| weburl | text | ||
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Project: A DOAP Project. This table is the core of the DOAP section of the Launchpad database. It contains details of a single open source Project and is the anchor point for products, potemplates, and translationefforts.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | owner | integer | NOT NULL The owner of the project will initially be the person who creates this Project in the system. We will encourage upstream project leaders to take on this role. The Project owner is able to edit the project. |
| name | text | UNIQUE NOT NULL A short lowercase name uniquely identifying the product. Use cases include being used as a key in URL traversal. |
|
| displayname | text | NOT NULL | |
| title | text | NOT NULL | |
| summary | text | NOT NULL A brief summary of this project. This will be displayed in bold text just above the description and below the title. It should be a single paragraph of not more than 80 words. |
|
| description | text | NOT NULL A detailed description of this project. This should primarily be focused on the organisational aspects of the project, such as the people involved and the structures that the project uses to govern itself. It might refer to the primary products of the project but the detailed descriptions of those products should be in the Product.description field, not here. So, for example, useful information such as the dates the project was started and the way the project coordinates itself are suitable here. |
|
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| homepageurl | text | The home page URL of this project. Note that this could well be the home page of the main product of this project as well, if the project is too small to have a separate home page for project and product. |
|
| wikiurl | text | This is the URL of a wiki that includes information about the project. It might be a page in a bigger wiki, or it might be the top page of a wiki devoted to this project. |
|
| lastdoap | text | This column stores a cached copy of the last DOAP description we saw for this project. We cache the last DOAP fragment for this project because there may be some aspects of it which we are unable to represent in the database (such as multiple homepageurl's instead of just a single homepageurl) and storing the DOAP file allows us to re-parse it later and recover this information when our database model has been updated appropriately. |
|
| sourceforgeproject | text | The SourceForge project name for this project. This is not unique as SourceForge doesn't use the same project/product structure as DOAP. |
|
| freshmeatproject | text | The FreshMeat project name for this project. This is not unique as FreshMeat does not have the same project/product structure as DOAP |
|
| reviewed | boolean | NOT NULL DEFAULT false Whether or not someone at Canonical has reviewed this project. |
|
| active | boolean | NOT NULL DEFAULT true Whether or not this project should be considered active. |
|
| fti | ts2.tsvector | ||
| public.translationgroup.id | translationgroup | integer | The translation group that has permission to edit translations across all products in this project. Note that individual products may have their own translationgroup, in which case those translators will also have permission to edit translations for that product. |
| translationpermission | integer | NOT NULL DEFAULT 1 The level of openness of this project's translation process. The enum lists different approaches to translation, from the very open (anybody can edit any translation in any language) to the completely closed (only designated translators can make any changes at all). |
|
| public.person.id | driver | integer | This person or team has the ability to approve specs as goals for any series in any product in the project. Similarly, this person or team can approve bugs as targets for fixing in any series, or backporting of fixes to any series. |
| public.bugtracker.id | bugtracker | integer | |
| homepage_content | text | A home page for this project in the Launchpad. |
|
| public.libraryfilealias.id | icon | integer | The library file alias to a small image to be used as an icon whenever we are referring to a project. |
| public.libraryfilealias.id | mugshot | integer | The library file alias of a mugshot image to display as the branding of a project, on its home page. |
| public.libraryfilealias.id | logo | integer | The library file alias of a smaller version of this product's mugshot. |
| bug_reporting_guidelines | text | Guidelines to the end user for reporting bugs on products in this project. |
|
| reviewer_whiteboard | text | A whiteboard for Launchpad admins, registry experts and the project owners to capture the state of current issues with the project. |
|
| public.person.id | registrant | integer | NOT NULL The registrant is the Person who created the project in Launchpad. It is set at creation and is never changed thereafter. |
| Name | Constraint |
|---|---|
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
project__icon__idx icon) WHERE (icon IS NOT NULL project__logo__idx logo) WHERE (logo IS NOT NULL project__mugshot__idx mugshot) WHERE (mugshot IS NOT NULL project__registrant__idx registrant project_fti fti project_owner_idx owner project_translationgroup_idx translationgroupThis table records a simple link between a bounty and a project. This bounty will be listed on the project web page, and the project will be mentioned on the bounty web page.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.bounty.id | bounty | integer | UNIQUE#1 NOT NULL |
| public.project.id | project | integer | UNIQUE#1 NOT NULL |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Project Relationships. This table stores information about the way projects are related to one another in the open source world. The actual nature of the relationship is stored in the 'label' field, and possible values are given by the ProjectRelationship enum in dbschema.py. Examples are AGGREGATES ("the Gnome Project AGGREGATES EOG and Evolution and Gnumeric and AbiWord") and SIMILAR ("the Evolution project is SIMILAR to the Mutt project").
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.project.id | subject | integer | NOT NULL The subject of the relationship. Relationships are generally unidirectional - A AGGREGATES B is not the same as B AGGREGATES A. In the example "Gnome AGGREGATES Evolution", Gnome is the subject. |
| label | integer | NOT NULL The nature of the relationship. This integer takes one of the values enumerated in dbschema.py ProjectRelationship |
|
| public.project.id | object | integer | NOT NULL The object of the relationship. In the example "Gnome AGGREGATES Evolution", Evolution is the object. |
A very large view that brings together all the information about packages that are currently being published within a distribution. This view was designed for the page which shows packages published in the distribution, but may be more widely used.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | integer | ||
| distroarchseries | integer | ||
| distribution | integer | ||
| distroseries | integer | ||
| distroseriesname | text | ||
| processorfamily | integer | ||
| processorfamilyname | text | ||
| packagepublishingstatus | integer | ||
| component | text | ||
| section | text | ||
| binarypackagerelease | integer | ||
| binarypackagename | text | ||
| binarypackagesummary | text | ||
| binarypackagedescription | text | ||
| binarypackageversion | text | ||
| build | integer | ||
| datebuilt | timestamp without time zone | ||
| sourcepackagerelease | integer | ||
| sourcepackagereleaseversion | text | ||
| sourcepackagename | text | ||
| integer | |||
| archive | integer | ||
| binarypackagefti | ts2.tsvector |
SELECT securebinarypackagepublishinghistory.id
, distroarchseries.id AS distroarchseries
, distroseries.distribution
, distroseries.id AS distroseries
, distroseries.name AS distroseriesname
, processorfamily.id AS processorfamily
, processorfamily.name AS processorfamilyname
, securebinarypackagepublishinghistory.status AS packagepublishingstatus
, component.name AS component
, section.name AS section
, binarypackagerelease.id AS binarypackagerelease
, binarypackagename.name AS binarypackagename
, binarypackagerelease.summary AS binarypackagesummary
, binarypackagerelease.description AS binarypackagedescription
, binarypackagerelease.version AS binarypackageversion
, build.id AS build
, build.datebuilt
, sourcepackagerelease.id AS sourcepackagerelease
, sourcepackagerelease.version AS sourcepackagereleaseversion
, sourcepackagename.name AS sourcepackagename
, securebinarypackagepublishinghistory.pocket
, securebinarypackagepublishinghistory.archive
, binarypackagerelease.fti AS binarypackagefti
FROM (
(
(
(
(
(
(
(
(
(securebinarypackagepublishinghistory
JOIN distroarchseries
ON (
(distroarchseries.id = securebinarypackagepublishinghistory.distroarchseries)
)
)
JOIN distroseries
ON (
(distroarchseries.distroseries = distroseries.id)
)
)
JOIN processorfamily
ON (
(distroarchseries.processorfamily = processorfamily.id)
)
)
JOIN component
ON (
(securebinarypackagepublishinghistory.component = component.id)
)
)
JOIN binarypackagerelease
ON (
(securebinarypackagepublishinghistory.binarypackagerelease = binarypackagerelease.id)
)
)
JOIN section
ON (
(securebinarypackagepublishinghistory.section = section.id)
)
)
JOIN binarypackagename
ON (
(binarypackagerelease.binarypackagename = binarypackagename.id)
)
)
JOIN build
ON (
(binarypackagerelease.build = build.id)
)
)
JOIN sourcepackagerelease
ON (
(build.sourcepackagerelease = sourcepackagerelease.id)
)
)
JOIN sourcepackagename
ON (
(sourcepackagerelease.sourcepackagename = sourcepackagename.id)
)
)
WHERE (securebinarypackagepublishinghistory.dateremoved IS NULL);
Records which users can update which push mirrors
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL Name of an arch archive on the push mirror, e.g. lord@emf.net--2003-example |
|
| public.person.id | person | integer | A person that has access to update the named archive |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
A question, or support request, for a distribution or for an application. Such questions are created by end users who need support on a particular feature or package or product.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | owner | integer | NOT NULL |
| title | text | NOT NULL | |
| description | text | NOT NULL | |
| public.person.id | assignee | integer | The person who has been assigned to resolve this question. Note that there is no requirement that every question be assigned somebody. Anybody can chip in to help resolve a question, and if they think they have done so we call them the "answerer". |
| public.person.id | answerer | integer | The person who last claimed to have "solved" this support question, giving a response that the owner believe should be sufficient to close the question. This will move the status of the question to "SOLVED". Note that the only person who can actually set the status to SOLVED is the person who asked the question. |
| public.product.id | product | integer | The upstream product to which this quesiton is related. Note that a quesiton MUST be linked either to a product, or to a distribution. |
| public.distribution.id | distribution | integer | The distribution for which a question was filed. Note that a request MUST be linked either to a product or a distribution. |
| public.sourcepackagename.id | sourcepackagename | integer | An optional source package name. This only makes sense if the question is bound to a distribution. |
| status | integer | NOT NULL | |
| priority | integer | NOT NULL | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| datelastquery | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The date we last saw a comment from the requester (owner). |
|
| dateaccepted | timestamp without time zone | The date we "confirmed" or "accepted" this question. It is usually set to the date of the first response by someone other than the requester. This allows us to track the time between first request and first response. |
|
| datedue | timestamp without time zone | The date this question is "due", if such a date can be established. Usually this will be set automatically on the basis of a support contract SLA commitment. |
|
| datelastresponse | timestamp without time zone | The date we last saw a comment from somebody other than the requester. |
|
| date_solved | timestamp without time zone | The date this question was last marked as solved by the requester (owner). The requester either found a solution, or accepted an answer from another user. |
|
| dateclosed | timestamp without time zone | The date the requester marked this question CLOSED. |
|
| whiteboard | text | A general status whiteboard. This is a scratch space to which arbitrary data can be added (there is only one constant whiteboard with no history). It is displayed at the top of the question. So its a useful way for projects to add their own semantics or metadata to the Answer Tracker. |
|
| fti | ts2.tsvector | ||
| public.questionmessage.id | answer | integer | The QuestionMessage that was accepted by the submitter as the "answer" to the question |
| public.language.id | language | integer | NOT NULL The language of the question's title and description. |
| public.faq.id | faq | integer | The FAQ document that contains the long answer to this question. |
| Name | Constraint |
|---|---|
| product_or_distro | CHECK (((product IS NULL) <> (distribution IS NULL))) |
| sourcepackagename_needs_distro | CHECK (((sourcepackagename IS NULL) OR (distribution IS NOT NULL))) |
Tables referencing this one via Foreign Key Constraints:
question__answerer__idx answerer question__assignee__idx assignee question__distribution__sourcepackagename__idx distribution, sourcepackagename question__distro__datecreated__idx distribution, datecreated question__faq__idx faq) WHERE (faq IS NOT NULL question__owner__idx assignee question__product__datecreated__idx product, datecreated question__product__idx product question__status__datecreated__idx status, datecreated question_fti ftiA link between a question and a bug, showing that the bug is somehow related to this question.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.question.id | question | integer | UNIQUE#1 NOT NULL |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
A link between a question and a message. This means that the message will be displayed on the question page.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.question.id | question | integer | UNIQUE#1 NOT NULL |
| public.message.id | message | integer | UNIQUE#1 NOT NULL |
| action | integer | NOT NULL The action on the question that was done with this message. This is a value from the QuestionAction enum. |
|
| new_status | integer | NOT NULL The status of the question after this message. |
Tables referencing this one via Foreign Key Constraints:
questionmessage__question__idx questionA record of the times when a question was re-opened. In each case we store the time that it happened, the person who did it, and the person who had previously answered / rejected the question.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.question.id | question | integer | NOT NULL |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | reopener | integer | NOT NULL The person who reopened the question. |
| public.person.id | answerer | integer | The person who was previously listed as the answerer of the question. |
| date_solved | timestamp without time zone | ||
| priorstate | integer | NOT NULL The state of the question before it was reopened. You can reopen a question that is ANSWERED, or CLOSED, or REJECTED. |
A subscription of a person to a particular question.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.question.id | question | integer | UNIQUE#1 NOT NULL |
| public.person.id | person | integer | UNIQUE#1 NOT NULL |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
The requested CDs of a Shipping Request.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.shippingrequest.id | request | integer | UNIQUE#1 NOT NULL The request itself. |
| quantity | integer | NOT NULL The number of CDs. |
|
| flavour | integer | UNIQUE#1 NOT NULL The flavour of the distroseries (e.g. EdUbuntu). |
|
| distroseries | integer | UNIQUE#1 NOT NULL The distroseries of the CDs (e.g. Ubuntu Breezy). |
|
| architecture | integer | UNIQUE#1 NOT NULL The architecture the CDs are meant to be installed on (e.g. x86). |
|
| quantityapproved | integer | NOT NULL The number of CDs that were approved for shipping, in case the request was approved. |
| Name | Constraint |
|---|---|
| quantity_is_positive | CHECK ((quantity >= 0)) |
| quantityapproved_is_positive | CHECK ((quantityapproved >= 0)) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY UNIQUE#1 | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| log_body | text | NOT NULL | |
| public.revisionauthor.id | revision_author | integer | NOT NULL |
| public.gpgkey.id | gpgkey | integer | |
| revision_id | text | UNIQUE NOT NULL | |
| revision_date | timestamp without time zone | UNIQUE#1 | |
| karma_allocated | boolean | DEFAULT false |
Tables referencing this one via Foreign Key Constraints:
changeset_datecreated_idx date_created revision__gpgkey__idx gpgkey) WHERE (gpgkey IS NOT NULL revision__karma_allocated__idx karma_allocated) WHERE (karma_allocated IS FALSE revision__revision_author__idx revision_author revision__revision_date__idx revision_dateAll distinct authors for revisions.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL The exact text extracted from the branch revision. |
|
| text | A valid email address extracted from the name. This email address may or may not be associated with a Launchpad user at this stage. |
||
| public.person.id | person | integer | The Launchpad person that has a verified email address that matches the email address of the revision author. |
Tables referencing this one via Foreign Key Constraints:
revisionauthor__email__idx email revisionauthor__lower_email__idx lower(email) revisionauthor__person__idx personA cache of revisions where the revision date is in the last 30 days.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.revision.id | revision | integer | NOT NULL A reference to the actual revision. |
| public.revisionauthor.id | revision_author | integer | NOT NULL A refernce to the revision author for the revision. |
| revision_date | timestamp without time zone | NOT NULL The date the revision was made. Should be within 30 days of today (or the cleanup code is not cleaning up). |
|
| public.product.id | product | integer | The product that the revision is found in (if it is indeed in a particular product). |
| public.distroseries.id | distroseries | integer | The distroseries for which a source package branch contains the revision. |
| public.sourcepackagename.id | sourcepackagename | integer | The sourcepackagename for which a source package branch contains the revision. |
| private | boolean | NOT NULL True if the revision is only found in private branches, False if it can be found in a non-private branch. |
| Name | Constraint |
|---|---|
| valid_target | CHECK ((((distroseries IS NULL) = (sourcepackagename IS NULL)) AND (((distroseries IS NULL) AND (product IS NULL)) OR ((distroseries IS NULL) <> (product IS NULL))))) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | integer | ||
| sequence | integer | ||
| branch | integer | ||
| revision | integer |
SELECT branchrevision.id , branchrevision.sequence , branchrevision.branch , branchrevision.revision FROM branchrevision;
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| sequence | integer | NOT NULL | |
| public.revision.id | revision | integer | UNIQUE#1 NOT NULL |
| parent_id | text | UNIQUE#1 NOT NULL |
A collection of name and value pairs that appear on a revision.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.revision.id | revision | integer | UNIQUE#1 NOT NULL The revision which has properties. |
| name | text | UNIQUE#1 NOT NULL The name of the property. |
|
| value | text | NOT NULL The value of the property. |
Records of successful runs of scripts
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | NOT NULL The name of the script |
|
| hostname | text | NOT NULL The hostname of the machine where the script was run |
|
| date_started | timestamp without time zone | NOT NULL The date at which the script started |
|
| date_completed | timestamp without time zone | NOT NULL The date at which the script completed |
Known sections in Launchpad
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL Section name text |
Tables referencing this one via Foreign Key Constraints:
Allowed sections in a given distroseries.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distroseries.id | distroseries | integer | NOT NULL Refers to the distroseries in question. |
| public.section.id | section | integer | NOT NULL Refers to the section in question. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
PackagePublishingHistory: The history of a BinaryPackagePublishing record. This table represents the lifetime of a publishing record from inception to deletion. Records are never removed from here and in time the publishing table may become a view onto this table. A column being NULL indicates there's no data for that state transition. E.g. a package which is removed without being superseded won't have datesuperseded or supersededby filled in.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.binarypackagerelease.id | binarypackagerelease | integer | NOT NULL The binarypackage being published. |
| public.distroarchseries.id | distroarchseries | integer | NOT NULL The distroarchseries into which the binarypackage is being published. |
| status | integer | NOT NULL The current status of the publishing. |
|
| public.component.id | component | integer | NOT NULL The component into which the publishing takes place. |
| public.section.id | section | integer | NOT NULL The section into which the publishing takes place. |
| priority | integer | NOT NULL The priority at which the publishing takes place. |
|
| datecreated | timestamp without time zone | NOT NULL The date/time on which the publishing record was created. |
|
| datepublished | timestamp without time zone | The date/time on which the source was actually published into an archive. |
|
| datesuperseded | timestamp without time zone | The date/time on which the source was superseded by a new source. |
|
| public.build.id | supersededby | integer | The build which superseded this package. This seems odd but it is important because a new build may not actually build a given binarypackage and we need to supersede it appropriately |
| datemadepending | timestamp without time zone | The date/time on which this publishing record was made to be pending removal from the archive. |
|
| scheduleddeletiondate | timestamp without time zone | The date/time at which the package is/was scheduled to be deleted. |
|
| dateremoved | timestamp without time zone | The date/time at which the package was actually deleted. |
|
| integer | NOT NULL The pocket into which this record is published. The RELEASE pocket (zero) provides behaviour as normal. Other pockets may append things to the distroseries name such as the UPDATES pocket (-updates) or the SECURITY pocket (-security). |
||
| embargo | boolean | NOT NULL DEFAULT false The publishing record is embargoed from publication if this is set to TRUE. When TRUE, this column prevents the publication record from even showing up in the publishing tables. |
|
| embargolifted | timestamp without time zone | The date and time when we lifted the embargo on this publishing record. I.E. when embargo was set to FALSE having previously been set to TRUE. |
|
| public.archive.id | archive | integer | NOT NULL Target archive for this publishing record. |
| public.person.id | removed_by | integer | Person responsible for the removal. |
| removal_comment | text | Reason why the publication was removed. |
SourcePackagePublishingHistory: The history of a SourcePackagePublishing record. This table represents the lifetime of a publishing record from inception to deletion. Records are never removed from here and in time the publishing table may become a view onto this table. A column being NULL indicates there's no data for that state transition. E.g. a package which is removed without being superseded won't have datesuperseded or supersededby filled in.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.sourcepackagerelease.id | sourcepackagerelease | integer | NOT NULL The sourcepackagerelease being published. |
| public.distroseries.id | distroseries | integer | NOT NULL The distroseries into which the sourcepackagerelease is being published. |
| status | integer | NOT NULL The current status of the publishing. |
|
| public.component.id | component | integer | NOT NULL The component into which the publishing takes place. |
| public.section.id | section | integer | NOT NULL The section into which the publishing takes place. |
| datecreated | timestamp without time zone | NOT NULL The date/time on which the publishing record was created. |
|
| datepublished | timestamp without time zone | The date/time on which the source was actually published into an archive. |
|
| datesuperseded | timestamp without time zone | The date/time on which the source was superseded by a new source. |
|
| public.sourcepackagerelease.id | supersededby | integer | The source which superseded this one. |
| datemadepending | timestamp without time zone | The date/time on which this publishing record was made to be pending removal from the archive. |
|
| scheduleddeletiondate | timestamp without time zone | The date/time at which the source is/was scheduled to be deleted. |
|
| dateremoved | timestamp without time zone | The date/time at which the source was actually deleted. |
|
| integer | NOT NULL The pocket into which this record is published. The RELEASE pocket (zero) provides behaviour as normal. Other pockets may append things to the distroseries name such as the UPDATES pocket (-updates), the SECURITY pocket (-security) and the PROPOSED pocket (-proposed) |
||
| embargo | boolean | NOT NULL DEFAULT false The publishing record is embargoed from publication if this is set to TRUE. When TRUE, this column prevents the publication record from even showing up in the publishing tables. |
|
| embargolifted | timestamp without time zone | The date and time when we lifted the embargo on this publishing record. I.E. when embargo was set to FALSE having previously been set to TRUE. |
|
| public.archive.id | archive | integer | NOT NULL The target archive for thi publishing record. |
| public.person.id | removed_by | integer | Person responsible for the removal. |
| removal_comment | text | Reason why the publication was removed. |
Link between branches and distribution suite.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.distroseries.id | distroseries | integer | UNIQUE#1 NOT NULL The distroseries the branch is linked to. |
| integer | UNIQUE#1 NOT NULL The pocket the branch is linked to. |
||
| public.sourcepackagename.id | sourcepackagename | integer | UNIQUE#1 NOT NULL The sourcepackagename the branch is linked to. |
| public.branch.id | branch | integer | NOT NULL The branch being linked to a distribution suite. |
| public.person.id | registrant | integer | NOT NULL The person who registered this link. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date this link was created. |
A report generated with the ShipIt data.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datecreated | timestamp without time zone | NOT NULL The date this report run was created. |
|
| public.libraryfilealias.id | csvfile | integer | NOT NULL A csv file with the report |
A sequence of questions and their answers given by a ShipIt user.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| account | integer | NOT NULL | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| exported | boolean | NOT NULL DEFAULT false |
Tables referencing this one via Foreign Key Constraints:
shipitsurvey__account__idx accountThe text of an answer given by ShipIt users. Answers are usually multiple choice, but freeform answers could be stored here too.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| answer | text | UNIQUE NOT NULL |
Tables referencing this one via Foreign Key Constraints:
The text of a question that is asked of ShipIt users.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| question | text | UNIQUE NOT NULL |
Tables referencing this one via Foreign Key Constraints:
A single element in a ShipItSurvey.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.shipitsurvey.id | survey | integer | NOT NULL |
| public.shipitsurveyquestion.id | question | integer | NOT NULL |
| public.shipitsurveyanswer.id | answer | integer | The given answer. NULL indicates not answered (which may be different to declined to answer). |
A shipment is the link between a ShippingRequest and a ShippingRun. When a Shipment is created for a ShippingRequest, it gets locked and can't be changed anymore.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| logintoken | text | UNIQUE NOT NULL A unique token used to identify users that come back after receiving CDs as part of an shock and awe campaign. |
|
| public.shippingrun.id | shippingrun | integer | NOT NULL The shippingrun to which this shipment belongs. |
| dateshipped | timestamp without time zone | The date when this shipment was shipped by the shipping company. |
|
| shippingservice | integer | NOT NULL The shipping service used for this shipment. |
|
| trackingcode | text | A code used to track the shipment after it's shipped. |
Tables referencing this one via Foreign Key Constraints:
shipment_shippingrun_idx shippingrunA shipping request made through ShipIt.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| recipient | integer | NOT NULL The person who requested. |
|
| whoapproved | integer | The person who approved this. |
|
| whocancelled | integer | The person who cancelled this. |
|
| daterequested | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date this request was made. |
|
| reason | text | A comment from the requester explaining why he want the CDs. |
|
| highpriority | boolean | NOT NULL DEFAULT false Is this a high priority request? |
|
| recipientdisplayname | text | NOT NULL Used as the recipient's name when a request is made by a ShipIt admin in behalf of someone else |
|
| addressline1 | text | NOT NULL The address (first line) to which this request should be shipped. |
|
| addressline2 | text | The address (second line) to which this request should be shipped. |
|
| organization | text | The organization requesting the CDs. |
|
| city | text | NOT NULL The city to which this request should be shipped. |
|
| province | text | The province to which this request should be shipped. |
|
| public.country.id | country | integer | NOT NULL The country to which this request should be shipped. |
| postcode | text | The postcode to which this request should be shipped. |
|
| phone | text | The phone number of the requester. |
|
| fti | ts2.tsvector | ||
| public.shipment.id | shipment | integer | UNIQUE The corresponding Shipment record for this request, generated on export. |
| status | integer | NOT NULL The status of the request. |
|
| normalized_address | text | NOT NULL The normalized address of this request. It is maintained by a trigger because it's safer than hacking sqlobject's internals (specially because we sometimes update data behind sqlobject's back). |
|
| type | integer | The type of the request. |
|
| is_admin_request | boolean | NOT NULL DEFAULT false |
| Name | Constraint |
|---|---|
| enforce_shipped_status | CHECK (((status <> 4) OR (shipment IS NOT NULL))) |
| printable_addresses | CHECK (is_printable_ascii((((((((COALESCE(recipientdisplayname, ''::text) || COALESCE(addressline1, ''::text)) || COALESCE(addressline2, ''::text)) || COALESCE(organization, ''::text)) || COALESCE(city, ''::text)) || COALESCE(province, ''::text)) || COALESCE(postcode, ''::text)) || COALESCE(phone, ''::text)))) |
Tables referencing this one via Foreign Key Constraints:
shippingrequest__daterequested__approved__idx daterequested) WHERE (status = 1 shippingrequest__daterequested__unapproved__idx daterequested) WHERE (status = 0 shippingrequest__normalized_address__idx normalized_address shippingrequest__recipientdisplayname__idx recipientdisplayname shippingrequest__whocancelled__idx whocancelled) WHERE (whocancelled IS NOT NULL shippingrequest_daterequested_idx daterequested shippingrequest_fti fti shippingrequest_highpriority_idx highpriority shippingrequest_recipient_idx recipient shippingrequest_whoapproved_idx whoapprovedA shipping run is a set of shipments that are sent to the shipping company in the same date.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datecreated | timestamp without time zone | NOT NULL The date this shipping run was created. |
|
| sentforshipping | boolean | NOT NULL DEFAULT false The exported file was sent to the shipping company already? |
|
| public.libraryfilealias.id | csvfile | integer | UNIQUE A csv file with all requests of this shipping run, to be sent to the shipping company. |
| requests_count | integer | NOT NULL A cache of the number of requests that are part of this ShippingRun, to avoid an expensive COUNT(*) query to get this data. |
Tables referencing this one via Foreign Key Constraints:
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id public.gpgkey.owner#1 | owner | integer | NOT NULL |
| public.gpgkey.id#1 | signingkey | integer | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| signedcode | text | ||
| recipient | integer | ||
| active | boolean | NOT NULL DEFAULT false | |
| admincomment | text |
This view is used mostly by Lucille while performing publishing and unpublishing operations. It lists all the files associated with a sourcepackagerelease and collates all the textual representations needed for publishing components etc to allow rapid queries from SQLObject.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | text | ||
| distribution | integer | ||
| sourcepackagepublishing | integer | ||
| libraryfilealias | integer | ||
| libraryfilealiasfilename | text | ||
| sourcepackagename | text | ||
| componentname | text | ||
| distroseriesname | text | ||
| publishingstatus | integer | ||
| integer | |||
| archive | integer |
SELECT (
(
(libraryfilealias.id)::text || '.'::text
) ||
(securesourcepackagepublishinghistory.id)::text
) AS id
, distroseries.distribution
, securesourcepackagepublishinghistory.id AS sourcepackagepublishing
, sourcepackagereleasefile.libraryfile AS libraryfilealias
, libraryfilealias.filename AS libraryfilealiasfilename
, sourcepackagename.name AS sourcepackagename
, component.name AS componentname
, distroseries.name AS distroseriesname
, securesourcepackagepublishinghistory.status AS publishingstatus
, securesourcepackagepublishinghistory.pocket
, securesourcepackagepublishinghistory.archive
FROM (
(
(
(
(
(securesourcepackagepublishinghistory
JOIN sourcepackagerelease
ON (
(securesourcepackagepublishinghistory.sourcepackagerelease = sourcepackagerelease.id)
)
)
JOIN sourcepackagename
ON (
(sourcepackagerelease.sourcepackagename = sourcepackagename.id)
)
)
JOIN sourcepackagereleasefile
ON (
(sourcepackagereleasefile.sourcepackagerelease = sourcepackagerelease.id)
)
)
JOIN libraryfilealias
ON (
(libraryfilealias.id = sourcepackagereleasefile.libraryfile)
)
)
JOIN distroseries
ON (
(securesourcepackagepublishinghistory.distroseries = distroseries.id)
)
)
JOIN component
ON (
(securesourcepackagepublishinghistory.component = component.id)
)
)
WHERE (securesourcepackagepublishinghistory.dateremoved IS NULL);
SourcePackageName: A soyuz source package name.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL A lowercase name identifying one or more sourcepackages |
| Name | Constraint |
|---|---|
| valid_name | CHECK (valid_name(name)) |
Tables referencing this one via Foreign Key Constraints:
A view on SecureSourcePackagePublishingHistory that restricts access to embargoed entries
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | integer | ||
| sourcepackagerelease | integer | ||
| status | integer | ||
| component | integer | ||
| section | integer | ||
| distroseries | integer | ||
| integer | |||
| archive | integer | ||
| datecreated | timestamp without time zone | ||
| datepublished | timestamp without time zone | ||
| datesuperseded | timestamp without time zone | ||
| supersededby | integer | ||
| datemadepending | timestamp without time zone | ||
| scheduleddeletiondate | timestamp without time zone | ||
| dateremoved | timestamp without time zone | ||
| removed_by | integer | ||
| removal_comment | text | ||
| embargo | boolean | ||
| embargolifted | timestamp without time zone |
SELECT securesourcepackagepublishinghistory.id , securesourcepackagepublishinghistory.sourcepackagerelease , securesourcepackagepublishinghistory.status , securesourcepackagepublishinghistory.component , securesourcepackagepublishinghistory.section , securesourcepackagepublishinghistory.distroseries , securesourcepackagepublishinghistory.pocket , securesourcepackagepublishinghistory.archive , securesourcepackagepublishinghistory.datecreated , securesourcepackagepublishinghistory.datepublished , securesourcepackagepublishinghistory.datesuperseded , securesourcepackagepublishinghistory.supersededby , securesourcepackagepublishinghistory.datemadepending , securesourcepackagepublishinghistory.scheduleddeletiondate , securesourcepackagepublishinghistory.dateremoved , securesourcepackagepublishinghistory.removed_by , securesourcepackagepublishinghistory.removal_comment , securesourcepackagepublishinghistory.embargo , securesourcepackagepublishinghistory.embargolifted FROM securesourcepackagepublishinghistory WHERE (securesourcepackagepublishinghistory.embargo = false);
SourcePackageRelease: A source package release. This table represents a specific release of a source package. Source package releases may be published into a distroseries, or even multiple distroseries.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | creator | integer | NOT NULL The creator of this sourcepackagerelease. This is the person referred to in the top entry in the package changelog in debian terms. Note that a source package maintainer in Ubuntu might be person A, but a particular release of that source package might in fact have been created by a different person B. The maintainer would be recorded in the Maintainership table, while the creator of THIS release would be recorded in the SourcePackageRelease.creator field. |
| version | text | NOT NULL The version string for this source package release. E.g. "1.0-2" or "1.4-5ubuntu9.1". Note that, in ubuntu-style and redhat-style distributions, the version+sourcepackagename is unique, even across distroseries. In other words, you cannot have a foo-1.2-1 package in Hoary that is different from foo-1.2-1 in Warty. |
|
| dateuploaded | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The date/time that this sourcepackagerelease was first uploaded to the Launchpad. |
|
| urgency | integer | NOT NULL The urgency of the upload. This is generally used to prioritise buildd activity but may also be used for "testing" systems or security work in the future. The "urgency" is set by the uploader, in the DSC file. |
|
| public.gpgkey.id | dscsigningkey | integer | The GPG key used to sign the DSC. This is not necessarily the maintainer's key, or the creator's key. For example, it's possible to produce a package, then ask a sponsor to upload it. |
| public.component.id | component | integer | The component in which this sourcepackagerelease is intended (by the uploader) to reside. E.g. main, universe, restricted. Note that the distribution managers will often override this data and publish the package in an entirely different component. |
| changelog_entry | text | Changelog text section extracted from the changesfile. |
|
| builddepends | text | The build dependencies for this source package release. |
|
| builddependsindep | text | The architecture-independant build dependancies for this source package release. |
|
| architecturehintlist | text | NOT NULL The architectures which this source package release believes it should be built. This is used as a hint to the build management system when deciding what builds are still needed. |
|
| dsc | text | The "Debian Source Control" file for the sourcepackagerelease, from its upload into Ubuntu for the first time. |
|
| public.section.id | section | integer | NOT NULL This integer field references the Section which the source package claims to be in |
| public.person.id | maintainer | integer | NOT NULL Reference to the person noted as source package maintainer in the DSC. |
| public.sourcepackagename.id | sourcepackagename | integer | NOT NULL Reference to a SourcePackageName. |
| public.distroseries.id | upload_distroseries | integer | NOT NULL The distroseries into which this source package release was uploaded into Launchpad / Ubuntu for the first time. In general, this will be the development Ubuntu release into which this package was uploaded. For a package which was unchanged between warty and hoary, this would show Warty. For a package which was uploaded into Hoary, this would show Hoary. |
| format | integer | NOT NULL The format of this sourcepackage release, e.g. DPKG, RPM, EBUILD, etc. This is an enum, and the values are listed in dbschema.SourcePackageFormat |
|
| dsc_maintainer_rfc822 | text | The original maintainer line in RFC-822 format, to be used in archive indexes. |
|
| dsc_standards_version | text | DSC standards version (such as "3.6.2", "3.5.9", etc) used to build this source. |
|
| dsc_format | text | DSC format version (such as "1.0"). |
|
| dsc_binaries | text | DSC binary line, claimed binary-names produce by this source. |
|
| public.archive.id | upload_archive | integer | NOT NULL The archive into which this sourcepackagerelese was originally uploaded. |
| copyright | text | The copyright associated with this sourcepackage. Often in the case of debian packages and will be found after the installation in /usr/share/doc/<binarypackagename>/copyright |
|
| build_conflicts | text | The list of packages that will conflict with this source while building, as mentioned in the control file "Build-Conflicts:" field. |
|
| build_conflicts_indep | text | The list of packages that will conflict with this source while building in architecture independent environment, as mentioned in the control file "Build-Conflicts-Indep:" field. |
| Name | Constraint |
|---|---|
| valid_version | CHECK (valid_debian_version(version)) |
Tables referencing this one via Foreign Key Constraints:
sourcepackagerelease__upload_archive__idx upload_archive sourcepackagerelease_creator_idx creator sourcepackagerelease_maintainer_idx maintainer sourcepackagerelease_sourcepackagename_idx sourcepackagename sourcepackagerelease_version_sort debversion_sort_key(version)SourcePackageReleaseFile: A soyuz source package release file. This table links sourcepackagereleasehistory records to the files which comprise the input.
| F-Key | Name | Type | Description |
|---|---|---|---|
| public.sourcepackagerelease.id | sourcepackagerelease | integer | NOT NULL The sourcepackagerelease that this file belongs to |
| public.libraryfilealias.id | libraryfile | integer | NOT NULL The libraryfilealias embodying this file |
| filetype | integer | NOT NULL The type of the file. E.g. TAR, DIFF, DSC |
|
| id | serial | PRIMARY KEY |
A feature specification. At the moment we do not store the actual specification, we store a URL for the spec, which is managed in a wiki somewhere else. We store the overall state of the spec, as well as queueing information about who needs to review the spec, and why.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE#1 UNIQUE#2 NOT NULL | |
| title | text | NOT NULL | |
| summary | text | ||
| public.person.id | owner | integer | NOT NULL |
| public.person.id | assignee | integer | The person who has been assigned to implement this specification. |
| public.person.id | drafter | integer | The person who has been asked to draft this specification. They are responsible for getting the spec to "approved" state. |
| public.person.id | approver | integer | The person who is responsible for approving the specification in due course, and who will probably be required to review the code itself when it is being implemented. |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.product.id public.milestone.product#3 public.productseries.product#4 | product | integer | UNIQUE#2 The product for which this is a feature specification. The specification must be connected either to a product, or to a distribution. |
| public.productseries.id#4 | productseries | integer | This is an indicator that the specification is planned, or targeted, for implementation in a given product series. It is not necessary to target a spec to a series, but it is a useful way of showing which specs are planned to implement for a given series. |
| public.distroseries.distribution#1 public.distribution.id public.milestone.distribution#2 | distribution | integer | UNIQUE#1 The distribution for which this is a feature specification. The specification must be connected either to a product, or to a distribution. |
| public.distroseries.id#1 | distroseries | integer | If this is not NULL, then it means that the release managers have targeted this feature to be released in the given distroseries. It is not necessary to target a distroseries, but this is a useful way of know which specifications are, for example, BreezyGoals. |
| public.milestone.id#2 public.milestone.id#3 | milestone | integer | This is an indicator that the feature defined in this specification is expected to be delivered for a given milestone. Note that milestones are not necessarily releases, they are a way of identifying a point in time and grouping bugs and features around that. |
| definition_status | integer | NOT NULL An enum called SpecificationDefinitionStatus that shows what the current status (new, draft, implemented etc) the spec is currently in. |
|
| priority | integer | NOT NULL DEFAULT 5 An enum that gives the implementation priority (low, medium, high, emergency) of the feature defined in this specification. |
|
| specurl | text | UNIQUE The URL where the specification itself can be found. This is usually a wiki page somewhere. |
|
| whiteboard | text | As long as the specification is somewhere else (i.e. not in Launchpad) it will be useful to have a place to hold some arbitrary message or status flags that have meaning to the project, not Launchpad. This whiteboard is just the place for it. |
|
| public.specification.id | superseded_by | integer | The specification which replaced this specification. |
| direction_approved | boolean | NOT NULL DEFAULT false | |
| man_days | integer | ||
| implementation_status | integer | NOT NULL The implementation status of this specification. This field is used to track the actual delivery of the feature (implementing the spec), as opposed to the definition of expected behaviour (writing the spec). |
|
| goalstatus | integer | NOT NULL DEFAULT 30 Whether or not the drivers for the goal product series or distro release have accepted this specification as a goal. |
|
| fti | ts2.tsvector | ||
| public.person.id | goal_proposer | integer | The person who proposed this spec as a goal for the productseries or distroseries. |
| date_goal_proposed | timestamp without time zone | The date the spec was proposed as a goal. |
|
| public.person.id | goal_decider | integer | The person who approved or declined this goal. |
| date_goal_decided | timestamp without time zone | The date this goal was accepted or declined. |
|
| public.person.id | completer | integer | The person who changed the state of the spec in such a way that it was determined to be completed. |
| date_completed | timestamp without time zone | The date this specification was completed or marked obsolete. This lets us chart the progress of a project (or a release) over time in terms of features implemented. |
|
| public.person.id | starter | integer | |
| date_started | timestamp without time zone | ||
| private | boolean | NOT NULL DEFAULT false Specification is private. |
| Name | Constraint |
|---|---|
| distribution_and_distroseries | CHECK (((distroseries IS NULL) OR (distribution IS NOT NULL))) |
| product_and_productseries | CHECK (((productseries IS NULL) OR (product IS NOT NULL))) |
| product_xor_distribution | CHECK (((product IS NULL) <> (distribution IS NULL))) |
| specification_completion_fully_recorded_chk | CHECK (((date_completed IS NULL) = (completer IS NULL))) |
| specification_completion_recorded_chk | CHECK (((date_completed IS NULL) <> (((implementation_status = 90) OR (definition_status = ANY (ARRAY[60, 70]))) OR ((implementation_status = 95) AND (definition_status = 10))))) |
| specification_decision_recorded | CHECK (((goalstatus = 30) OR ((goal_decider IS NOT NULL) AND (date_goal_decided IS NOT NULL)))) |
| specification_goal_nomination_chk | CHECK ((((productseries IS NULL) AND (distroseries IS NULL)) OR ((goal_proposer IS NOT NULL) AND (date_goal_proposed IS NOT NULL)))) |
| specification_not_self_superseding | CHECK ((superseded_by <> id)) |
| specification_start_fully_recorded_chk | CHECK (((date_started IS NULL) = (starter IS NULL))) |
| specification_start_recorded_chk | CHECK (((date_started IS NULL) <> ((implementation_status <> ALL (ARRAY[0, 5, 10, 95])) OR ((implementation_status = 95) AND (definition_status = 10))))) |
| valid_name | CHECK (valid_name(name)) |
| valid_url | CHECK (valid_absolute_url(specurl)) |
Tables referencing this one via Foreign Key Constraints:
specification__completer__idx completer specification__goal_decider__idx goal_decider specification__goal_proposer__idx goal_proposer specification__starter__idx starter specification_approver_idx approver specification_assignee_idx assignee specification_datecreated_idx datecreated specification_drafter_idx drafter specification_fti fti specification_owner_idx ownerA branch related to a specification, most likely a branch for implementing the specification. It is possible to have multiple branches for a given specification especially in the situation where the specification requires modifying multiple products.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.specification.id | specification | integer | UNIQUE#1 NOT NULL The specification associated with this branch. |
| public.branch.id | branch | integer | UNIQUE#1 NOT NULL The branch associated to the specification. |
| summary | text | ||
| public.person.id | registrant | integer | NOT NULL The person who linked the specification to the branch. |
A table linking a specification and a bug. This is used to provide for easy navigation from bugs to related specs, and vice versa.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.specification.id | specification | integer | UNIQUE#1 NOT NULL |
| public.bug.id | bug | integer | UNIQUE#1 NOT NULL |
A table that stores information about which specification needs to be implemented before another specification can be implemented. We can create a chain of dependencies, and use that information for scheduling and prioritisation of work.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.specification.id | specification | integer | UNIQUE#1 NOT NULL The spec for which we are creating a dependency. |
| public.specification.id | dependency | integer | UNIQUE#1 NOT NULL The spec on which it is dependant. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
| Name | Constraint |
|---|---|
| specificationdependency_not_self | CHECK ((specification <> dependency)) |
A table representing a review request of a specification, from one user to another, with an optional message.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.specification.id | specification | integer | UNIQUE#1 NOT NULL |
| public.person.id | reviewer | integer | UNIQUE#1 NOT NULL The person who has been asked to do the review. |
| public.person.id | requester | integer | UNIQUE#1 NOT NULL The person who made the request. |
| queuemsg | text | An optional text message for the reviewer, from the requester. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
Comments and discussion on a Specification.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.specification.id | specification | integer | UNIQUE#1 |
| public.message.id | message | integer | UNIQUE#1 |
| visible | boolean | NOT NULL DEFAULT true |
A table capturing a subscription of a person to a specification.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.specification.id | specification | integer | UNIQUE#1 NOT NULL |
| public.person.id | person | integer | UNIQUE#1 NOT NULL |
| essential | boolean | NOT NULL DEFAULT false A field that indicates whether or not this person is essential to discussions on the planned feature. This is used by the meeting scheduler to ensure that all the essential people are at any automatically scheduled BOFs discussing that spec. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| public.language.id | language | integer | UNIQUE#1 NOT NULL |
| public.country.id | country | integer | UNIQUE#1 NOT NULL |
| id | serial | PRIMARY KEY |
A meeting, sprint or conference. This is a convenient way to keep track of a collection of specs that will be discussed, and the people that will be attending.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | owner | integer | NOT NULL |
| name | text | UNIQUE NOT NULL | |
| title | text | NOT NULL | |
| summary | text | NOT NULL | |
| home_page | text | ||
| address | text | ||
| time_zone | text | NOT NULL The timezone of the sprint, stored in text format from the Olsen database names, like "US/Eastern". |
|
| time_starts | timestamp without time zone | NOT NULL | |
| time_ends | timestamp without time zone | NOT NULL | |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | driver | integer | The driver (together with the registrant or owner) is responsible for deciding which topics will be accepted onto the agenda of the sprint. |
| homepage_content | text | A home page for this sprint in the Launchpad. |
|
| public.libraryfilealias.id | icon | integer | The library file alias to a small image to be used as an icon whenever we are referring to a sprint. |
| public.libraryfilealias.id | mugshot | integer | The library file alias of a mugshot image to display as the branding of a sprint, on its home page. |
| public.libraryfilealias.id | logo | integer | The library file alias of a smaller version of this sprint's mugshot. |
| Name | Constraint |
|---|---|
| sprint_starts_before_ends | CHECK ((time_starts < time_ends)) |
Tables referencing this one via Foreign Key Constraints:
sprint__driver__idx driver sprint__icon__idx icon) WHERE (icon IS NOT NULL sprint__logo__idx logo) WHERE (logo IS NOT NULL sprint__mugshot__idx mugshot) WHERE (mugshot IS NOT NULL sprint_datecreated_idx datecreatedThe record that someone will be attending a particular sprint or meeting.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | attendee | integer | UNIQUE#1 NOT NULL |
| public.sprint.id | sprint | integer | UNIQUE#1 NOT NULL |
| time_starts | timestamp without time zone | NOT NULL The time from which the person will be available to participate in meetings at the sprint. |
|
| time_ends | timestamp without time zone | NOT NULL The time of departure from the sprint or conference - this is the last time at which the person is available for meetings during the sprint. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
| Name | Constraint |
|---|---|
| sprintattendance_starts_before_ends | CHECK ((time_starts < time_ends)) |
The link between a sprint and a specification, so that we know which specs are going to be discussed at which sprint.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.sprint.id | sprint | integer | UNIQUE#1 NOT NULL |
| public.specification.id | specification | integer | UNIQUE#1 NOT NULL |
| status | integer | NOT NULL DEFAULT 30 Whether or not the spec has been approved on the agenda for this sprint. |
|
| whiteboard | text | A place to store comments specifically related to this spec being on the agenda of this meeting. |
|
| public.person.id | registrant | integer | NOT NULL The person who nominated this specification for the agenda of the sprint. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.person.id | decider | integer | The person who approved or declined this specification for the sprint agenda. |
| date_decided | timestamp without time zone | The date this specification was approved or declined for the agenda. |
| Name | Constraint |
|---|---|
| sprintspecification_decision_recorded | CHECK (((status = 30) OR ((decider IS NOT NULL) AND (date_decided IS NOT NULL)))) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | |
| keytype | integer | NOT NULL | |
| keytext | text | NOT NULL | |
| comment | text | NOT NULL | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) |
The Standard ShipIt Requests. This is what we want most of the people to choose, having only a few people placing custom requests.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| quantityx86 | integer | UNIQUE#1 NOT NULL The quantity of X86 CDs |
|
| quantityppc | integer | UNIQUE#1 NOT NULL The quantity of PowerPC CDs |
|
| quantityamd64 | integer | UNIQUE#1 NOT NULL The quantity of AMD64 CDs |
|
| isdefault | boolean | NOT NULL DEFAULT false Is this the order that is pre-selected in the options we give for the user? |
|
| flavour | integer | UNIQUE#1 NOT NULL The Distribution Flavour |
|
| description | text | The description of this option. |
| Name | Constraint |
|---|---|
| quantityamd64_is_positive | CHECK ((quantityamd64 >= 0)) |
| quantityppc_is_positive | CHECK ((quantityppc >= 0)) |
| quantityx86_is_positive | CHECK ((quantityx86 >= 0)) |
Information about static diffs.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| from_revision_id | text | UNIQUE#1 NOT NULL The revision-id that the diff is from. |
|
| to_revision_id | text | UNIQUE#1 NOT NULL The revision-id that the diff is to. |
|
| public.diff.id | diff | integer | NOT NULL The Diff. |
Tables referencing this one via Foreign Key Constraints:
staticdiff__diff__idx diffA subscription to notifications about a Launchpad structure
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.product.id | product | integer | The subscription`s target, when it is a product. |
| public.productseries.id | productseries | integer | The subscription`s target, when it is a product series. |
| public.project.id | project | integer | The subscription`s target, when it is a project. |
| public.milestone.id | milestone | integer | The subscription`s target, when it is a milestone. |
| public.distribution.id | distribution | integer | The subscription`s target, when it is a distribution. |
| public.distroseries.id | distroseries | integer | The subscription`s target, when it is a distribution series. |
| public.sourcepackagename.id | sourcepackagename | integer | The subscription`s target, when it is a source-package |
| public.person.id | subscriber | integer | NOT NULL The person subscribed. |
| public.person.id | subscribed_by | integer | NOT NULL The person initiating the subscription. |
| bug_notification_level | integer | NOT NULL The volume and type of bug notifications this subscription will generate. The value is an item of the enumeration `BugNotificationLevel`. |
|
| blueprint_notification_level | integer | NOT NULL The volume and type of blueprint notifications this subscription will generate. The value is an item of the enumeration `BugNotificationLevel`. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date on which this subscription was created. |
|
| date_last_updated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date on which this subscription was last updated. |
| Name | Constraint |
|---|---|
| one_target | CHECK ((null_count(ARRAY[product, productseries, project, distroseries, distribution, milestone]) = 5)) |
| sourcepackagename_requires_distribution | CHECK (((sourcepackagename IS NULL) OR (distribution IS NOT NULL))) |
The direct membership of a person on a given team.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | UNIQUE#1 NOT NULL The person. |
| public.person.id | team | integer | UNIQUE#1 NOT NULL The team. |
| status | integer | NOT NULL The state of the membership. |
|
| date_joined | timestamp without time zone | DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6)
with time zone) The date this membership was made active for the first time. |
|
| date_expires | timestamp without time zone | The date this membership will expire, if any. |
|
| public.person.id | last_changed_by | integer | The person who reviewed the last change to this membership. |
| last_change_comment | text | The comment left by the reviewer for the change. |
|
| public.person.id | proposed_by | integer | The user who proposed the person as member of the team. |
| public.person.id | acknowledged_by | integer | The member (or someone acting on his behalf) who accepts an invitation to join a team |
| public.person.id | reviewed_by | integer | The team admin who reviewed (approved/declined) the membership. |
| date_proposed | timestamp without time zone | The date of the proposal. |
|
| date_last_changed | timestamp without time zone | The date this membership was last changed. |
|
| date_acknowledged | timestamp without time zone | The date of acknowledgement. |
|
| date_reviewed | timestamp without time zone | The date the membership was approved/declined. |
|
| proponent_comment | text | The comment left by the proponent. |
|
| acknowledger_comment | text | The comment left by the person who acknowledged the membership. |
|
| reviewer_comment | text | The comment left by the approver. |
|
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date this membership was created. |
The participation of a person on a team, which can be a direct or indirect membership.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | team | integer | UNIQUE#1 NOT NULL The team. |
| public.person.id | person | integer | UNIQUE#1 NOT NULL The member. |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| uuid | text | UNIQUE NOT NULL | |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) | |
| public.libraryfilealias.id | file_alias | integer | UNIQUE NOT NULL |
This represents an organised translation group that spans multiple languages. Effectively it consists of a list of people (pointers to Person), and each Person is associated with a Language. So, for each TranslationGroup we can ask the question "in this TranslationGroup, who is responsible for translating into Arabic?", for example.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| name | text | UNIQUE NOT NULL | |
| title | text | ||
| summary | text | ||
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| public.person.id | owner | integer | NOT NULL |
| translation_guide_url | text | URL with documentation about general rules for translation work done by this translation group. |
Tables referencing this one via Foreign Key Constraints:
Queue with translatable resources pending to be imported into Rosetta.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| path | text | NOT NULL The path (included the filename) where this file was stored when we imported it. |
|
| public.libraryfilealias.id | content | integer | NOT NULL The file content that is being imported. |
| public.person.id | importer | integer | NOT NULL The person that did the import. |
| dateimported | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The timestamp when the import was done. |
|
| public.distroseries.id | distroseries | integer | The distribution release related to this import. |
| public.sourcepackagename.id | sourcepackagename | integer | The source package name related to this import. |
| public.productseries.id | productseries | integer | The product series related to this import. |
| is_published | boolean | NOT NULL Notes whether is a published upload. |
|
| public.pofile.id | pofile | integer | Link to the POFile where this import will end. |
| public.potemplate.id | potemplate | integer | Link to the POTemplate where this import will end. |
| status | integer | NOT NULL DEFAULT 5 The status of the import: 1 Approved, 2 Imported, 3 Deleted, 4 Failed, 5 Needs Review, 6 Blocked. |
|
| date_status_changed | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text,
('now'::text)::timestamp(6) with time zone) The date when the status of this entry was changed. |
|
| format | integer | NOT NULL DEFAULT 1 The file format of the content that is being imported. |
|
| error_output | text | Error output from last import attempt. |
| Name | Constraint |
|---|---|
| valid_link | CHECK ((((productseries IS NULL) <> (distroseries IS NULL)) AND ((distroseries IS NULL) = (sourcepackagename IS NULL)))) |
This table stores a concrete translation for a POTMsgSet message. It knows who, when and where did it, and whether it was reviewed by someone and when was it reviewed.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.pofile.id | pofile | integer | The translation file which this translation message is part of. |
| public.potmsgset.id | potmsgset | integer | NOT NULL The template message which this translation message is a translation of. |
| date_created | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date we saw this translation first. |
|
| public.person.id | submitter | integer | NOT NULL The person that made the submission through the web to Launchpad, or the last translator on the translation file that we are processing, or the person who uploaded that pofile to Launchpad. In short, our best guess as to the person who is contributing that translation. |
| date_reviewed | timestamp without time zone | The date when this message was reviewed for last time. |
|
| public.person.id | reviewer | integer | The person who did the review and accepted current translations. |
| public.potranslation.id | msgstr0 | integer | Translation for plural form 0 (if any). |
| public.potranslation.id | msgstr1 | integer | Translation for plural form 1 (if any). |
| public.potranslation.id | msgstr2 | integer | Translation for plural form 2 (if any). |
| public.potranslation.id | msgstr3 | integer | Translation for plural form 3 (if any). |
| comment | text | Text of translator comment from the translation file. |
|
| origin | integer | NOT NULL The source of this translation. This indicates whether the translation was in a translation file that we parsed (probably one published in a package or branch or tarball), in which case its value will be 1, or was submitted through the web, in which case its value will be 2. |
|
| validation_status | integer | NOT NULL Whether we have validated this translation. Being 0 the value that says this row has not been validated yet, 1 the value that says it is correct and 2 the value noting that there was an unknown error with the validation. |
|
| is_current | boolean | NOT NULL DEFAULT false Whether this translation is being used in Launchpad. |
|
| is_fuzzy | boolean | NOT NULL DEFAULT false | |
| is_imported | boolean | NOT NULL DEFAULT false Whether this translation is being used in latest imported file. |
|
| was_obsolete_in_last_import | boolean | NOT NULL DEFAULT false Whether this translation was obsolete in last imported file. |
|
| was_fuzzy_in_last_import | boolean | NOT NULL DEFAULT false | |
| public.potranslation.id | msgstr4 | integer | |
| public.potranslation.id | msgstr5 | integer | |
| public.potemplate.id | potemplate | integer | |
| public.language.id | language | integer | |
| variant | text |
| Name | Constraint |
|---|---|
| translationmessage__reviewer__date_reviewed__valid | CHECK (((reviewer IS NULL) = (date_reviewed IS NULL))) |
Tables referencing this one via Foreign Key Constraints:
tm__potmsgset__language__variant__not_used__idx potmsgset, language, variant) WHERE (NOT ((is_current IS TRUE) AND (is_imported IS TRUE)) translationmessage__current_or_imported__idx potmsgset) WHERE ((is_current IS TRUE) OR (is_imported IS TRUE) translationmessage__language__no_variant__submitter__idx language, submitter) WHERE (variant IS NULL translationmessage__language__variant__submitter__idx language, variant, submitter) WHERE (variant IS NOT NULL translationmessage__msgstr0__idx msgstr0 translationmessage__msgstr1__idx msgstr1) WHERE (msgstr1 IS NOT NULL translationmessage__msgstr2__idx msgstr2) WHERE (msgstr2 IS NOT NULL translationmessage__msgstr3__idx msgstr3) WHERE (msgstr3 IS NOT NULL translationmessage__msgstr4__idx msgstr4) WHERE (msgstr4 IS NOT NULL translationmessage__msgstr5__idx msgstr5) WHERE (msgstr5 IS NOT NULL translationmessage__pofile__idx pofile translationmessage__potmsgset__idx potmsgset translationmessage__potmsgset__language__idx potmsgset, language translationmessage__reviewer__idx reviewer translationmessage__submitter__idx submitterWho of translation contributors wants their translations relicensed and who does not.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | UNIQUE NOT NULL A translator which has submitted their answer. |
| allow_relicensing | boolean | NOT NULL DEFAULT true Does this person want their translations relicensed under BSD. |
|
| date_decided | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) Date when the last change of opinion was registered. |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.potemplate.id | potemplate | integer | NOT NULL |
| sequence | integer | NOT NULL | |
| public.potmsgset.id | potmsgset | integer | NOT NULL |
| Name | Constraint |
|---|---|
| translationtemplateitem_sequence_check | CHECK ((sequence >= 0)) |
A translator is a person in a TranslationGroup who is responsible for a particular language. At the moment, there can only be one person in a TranslationGroup who is the Translator for a particular language. If you want multiple people, then create a launchpad team and assign that team to the language.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.translationgroup.id | translationgroup | integer | UNIQUE#1 NOT NULL The TranslationGroup for which this Translator is working. |
| public.language.id | language | integer | UNIQUE#1 NOT NULL The language for which this Translator is responsible in this TranslationGroup. Note that the same person may be responsible for multiple languages, but any given language can only have one Translator within the TranslationGroup. |
| public.person.id | translator | integer | NOT NULL The Person who is responsible for this language in this translation group. |
| datecreated | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) | |
| style_guide_url | text | URL with translation style guide of a particular translation team. |
A log of all direct user-to-user email contacts that have gone through Launchpad.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | sender | integer | NOT NULL The person sending this email. |
| public.person.id | recipient | integer | NOT NULL The person receiving this email. |
| date_sent | timestamp without time zone | NOT NULL DEFAULT timezone('UTC'::text, now()) The date the email was sent. |
|
| subject | text | NOT NULL The Subject: header. |
|
| message_id | text | NOT NULL The Message-ID: header. |
A materialized view listing the Person.ids of all valid people (but not teams).
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | integer |
SELECT emailaddress.person AS id
FROM emailaddress
, account
WHERE (
(
(
(emailaddress.account = account.id)
AND (emailaddress.person IS NOT NULL)
)
AND (emailaddress.status = 4)
)
AND (account.status = 20)
);
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | integer |
SELECT person.id
FROM (
(person
LEFT JOIN emailaddress
ON (
(person.id = emailaddress.person)
)
)
LEFT JOIN account
ON (
(emailaddress.account = account.id)
)
)
WHERE (
(person.teamowner IS NOT NULL)
OR (
(account.status = 20)
AND (emailaddress.status = 4)
)
);
The table where we store the actual votes of people. It may or may not have a reference to the person who voted, depending on the poll's secrecy.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | The person who voted. It's NULL for secret polls. |
| public.poll.id public.polloption.poll#1 | poll | integer | NOT NULL The poll for which this vote applies. |
| preference | integer | Used to identify in what order the options were chosen by a given user (in case of preferential voting). |
|
| public.polloption.id#1 | option | integer | The choosen option. |
| token | text | NOT NULL A unique token that's give to the user so he can change his vote later. |
Here we store who has already voted in a poll, to ensure they do not vote again, and potentially to notify people that they may still vote.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | UNIQUE#1 NOT NULL The person who voted. |
| public.poll.id | poll | integer | UNIQUE#1 NOT NULL The poll in which this person voted. |
A list of specifications of clients which should be denied access on the web service.
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| date_created | timestamp without time zone | DEFAULT timezone('UTC'::text, now()) When this ban was created. |
|
| public.person.id | person | integer | If set, all access by this person should be denied access. |
| public.oauthconsumer.id | consumer | integer | If set, all access by this consumer should be denied. |
| public.oauthaccesstoken.id | token | integer | If set, all all access using this token should be denied. |
| ip | inet | If set, all requests from that host or network should be denied. If either person, consumer or token is also set, then only requests matching both the IP and the other constraint will be denied. |
|
| active | boolean | DEFAULT true Is the ban still in effect? |
| Name | Constraint |
|---|---|
| at_least_one_spec | CHECK (((ip IS NOT NULL) OR (null_count(ARRAY[person, consumer, token]) < 3))) |
| person_or_consumer_or_token_or_none | CHECK ((null_count(ARRAY[person, consumer, token]) >= 2)) |
| F-Key | Name | Type | Description |
|---|---|---|---|
| id | serial | PRIMARY KEY | |
| public.person.id | person | integer | NOT NULL |
| wiki | text | UNIQUE#1 NOT NULL | |
| wikiname | text | UNIQUE#1 NOT NULL |
Kill all backend processes connected to the given database. Note that this is unlikely to work if you are connected to the database you are killing, as you are likely to kill your own connection before all the others have been killed.
import os
from signal import SIGTERM
plan = plpy.prepare(
"SELECT procpid FROM pg_stat_activity WHERE datname=$1", ['text']
)
success = True
for row in plpy.execute(plan, args):
try:
plpy.info("Killing %d" % row['procpid'])
os.kill(row['procpid'], SIGTERM)
except OSError:
success = False
return success
SECURITY DEFINER wrapper around pg_stat_activity allowing unprivileged users to access most of its information.
SELECT
datid, datname, procpid, usesysid, usename,
CASE
WHEN current_query LIKE '<IDLE>%'
OR current_query LIKE 'autovacuum:%'
THEN current_query
ELSE
'<HIDDEN>'
END AS current_query,
waiting, xact_start, query_start,
backend_start, client_addr, client_port
FROM pg_catalog.pg_stat_activity;
Raise an exception if the given database patch has not been applied.
rv = plpy.execute("""
SELECT * FROM LaunchpadDatabaseRevision
WHERE major=%d AND minor=%d AND patch=%d
""" % (major, minor, patch))
if len(rv) == 0:
raise Exception(
'patch-%d-%02d-%d not applied.' % (major, minor, patch))
else:
return True
Return a string suitable for sorting debian version strings on
# If this method is altered, then any functional indexes using it
# need to be rebuilt.
import re
VERRE = re.compile("(?:([0-9]+):)?(.+?)(?:-([^-]+))?$")
MAP = "0123456789ABCDEFGHIJKLMNOPQRSTUV"
epoch, version, release = VERRE.match(args[0]).groups()
key = []
for part, part_weight in ((epoch, 3000), (version, 2000), (release, 1000)):
if not part:
continue
i = 0
l = len(part)
while i != l:
c = part[i]
if c.isdigit():
key.append(part_weight)
j = i
while i != l and part[i].isdigit(): i += 1
key.append(part_weight+int(part[j:i] or "0"))
elif c == "~":
key.append(0)
i += 1
elif c.isalpha():
key.append(part_weight+ord(c))
i += 1
else:
key.append(part_weight+256+ord(c))
i += 1
if not key or key[-1] != part_weight:
key.append(part_weight)
key.append(part_weight)
key.append(1)
# Encode our key and return it
#
result = []
for value in key:
if not value:
result.append("000")
else:
element = []
while value:
element.insert(0, MAP[value & 0x1F])
value >>= 5
element_len = len(element)
if element_len < 3:
element.insert(0, "0"*(3-element_len))
elif element_len == 3:
pass
elif element_len < 35:
element.insert(0, MAP[element_len-4])
element.insert(0, "X")
elif element_len < 1027:
element.insert(0, MAP[(element_len-4) & 0x1F])
element.insert(0, MAP[(element_len-4) & 0x3E0])
element.insert(0, "Y")
else:
raise ValueError("Number too large")
result.extend(element)
return "".join(result)
from random import choice
# Non display confusing characters.
chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'
# Character length of tokens. Can be increased, decreased or even made
# random - Launchpad does not care. 7 means it takes 40 bytes to store
# a null-terminated Launchpad identity URL on the current domain name.
length=7
loop_count = 0
while loop_count < 20000:
# Generate a random openid_identifier
oid = ''.join(choice(chars) for count in range(length))
# Check if the oid is already in the db, although this is pretty
# unlikely
rv = plpy.execute("""
SELECT COUNT(*) AS num FROM Account WHERE openid_identifier = '%s'
""" % oid, 1)
if rv[0]['num'] == 0:
return oid
loop_count += 1
if loop_count == 1:
plpy.warning(
'Clash generating unique openid_identifier. '
'Increase length if you see this warning too much.')
plpy.error(
"Unable to generate unique openid_identifier. "
"Need to increase length of tokens.")
Return TRUE if any regular expressions stored in the NameBlacklist table match the givenname, otherwise return FALSE.
SELECT COALESCE(name_blacklist_match($1)::boolean, FALSE);
True if the given name identifies a person in the Person table
SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NULL;
True if the string is pure printable US-ASCII
import re, string
try:
text = args[0].decode("ASCII")
except UnicodeError:
return False
if re.search(r"^[%s]*$" % re.escape(string.printable), text) is None:
return False
return True
True if the given id identifies a team in the Person table
SELECT count(*)>0 FROM Person WHERE id=$1 AND teamowner IS NOT NULL;
True if the given name identifies a team in the Person table
SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NOT NULL;
Maintain Branch name cache when Distribution is modified.
BEGIN
IF OLD.id != NEW.id THEN
RAISE EXCEPTION 'Cannot change Distribution.id';
END IF;
IF OLD.name != NEW.name THEN
UPDATE Branch SET unique_name = NULL
FROM DistroSeries
WHERE Branch.distroseries = Distroseries.id
AND Distroseries.distribution = NEW.id;
END IF;
RETURN NULL;
END;
Maintain Branch name cache when Distroseries is modified.
BEGIN
IF OLD.id != NEW.id THEN
RAISE EXCEPTION 'Cannot change Distroseries.id';
END IF;
IF OLD.name != NEW.name THEN
UPDATE Branch SET unique_name = NULL
WHERE Branch.distroseries = NEW.id;
END IF;
RETURN NULL;
END;
Maintain Branch name cache when Person is modified.
DECLARE
v_branch RECORD;
BEGIN
IF OLD.id != NEW.id THEN
RAISE EXCEPTION 'Cannot change Person.id';
END IF;
IF OLD.name != NEW.name THEN
UPDATE Branch SET owner_name = NEW.name WHERE owner = NEW.id;
END IF;
RETURN NULL;
END;
Maintain Branch name cache when Product is modified.
DECLARE
v_branch RECORD;
BEGIN
IF OLD.id != NEW.id THEN
RAISE EXCEPTION 'Cannot change Product.id';
END IF;
IF OLD.name != NEW.name THEN
UPDATE Branch SET target_suffix = NEW.name WHERE product=NEW.id;
END IF;
RETURN NULL;
END;
Trigger maintaining the PillarName table
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO PillarName (name, distribution)
VALUES (NEW.name, NEW.id);
ELSIF NEW.name != OLD.name THEN
UPDATE PillarName SET name=NEW.name WHERE distribution=NEW.id;
END IF;
RETURN NULL; -- Ignored - this is an AFTER trigger
END;
Trigger maintaining the PillarName table
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO PillarName (name, product, active)
VALUES (NEW.name, NEW.id, NEW.active);
ELSIF NEW.name != OLD.name OR NEW.active != OLD.active THEN
UPDATE PillarName SET name=NEW.name, active=NEW.active
WHERE product=NEW.id;
END IF;
RETURN NULL; -- Ignored - this is an AFTER trigger
END;
Trigger maintaining the PillarName table
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO PillarName (name, project, active)
VALUES (NEW.name, NEW.id, NEW.active);
ELSIF NEW.name != OLD.name or NEW.active != OLD.active THEN
UPDATE PillarName SET name=NEW.name, active=NEW.active
WHERE project=NEW.id;
END IF;
RETURN NULL; -- Ignored - this is an AFTER trigger
END;
Trigger maintaining the POFileTranslator table
DECLARE
v_trash_old BOOLEAN;
BEGIN
-- If we are deleting a row, we need to remove the existing
-- POFileTranslator row and reinsert the historical data if it exists.
-- We also treat UPDATEs that change the key (submitter) the same
-- as deletes. UPDATEs that don't change these columns are treated like
-- INSERTs below.
IF TG_OP = 'INSERT' THEN
v_trash_old := FALSE;
ELSIF TG_OP = 'DELETE' THEN
v_trash_old := TRUE;
ELSE -- UPDATE
v_trash_old = (
OLD.submitter != NEW.submitter
);
END IF;
IF v_trash_old THEN
-- Was this somebody's most-recently-changed message?
-- If so, delete the entry for that change.
DELETE FROM POFileTranslator
WHERE latest_message = OLD.id;
IF FOUND THEN
-- We deleted the entry for somebody's latest contribution.
-- Find that person's latest remaining contribution and
-- create a new record for that.
INSERT INTO POFileTranslator (
person, pofile, latest_message, date_last_touched
)
SELECT DISTINCT ON (person, pofile.id)
new_latest_message.submitter AS person,
pofile.id,
new_latest_message.id,
greatest(new_latest_message.date_created,
new_latest_message.date_reviewed)
FROM POFile
JOIN TranslationTemplateItem AS old_template_item
ON OLD.potmsgset = old_template_item.potmsgset AND
old_template_item.potemplate = pofile.potemplate AND
pofile.language = OLD.language AND
pofile.variant IS NOT DISTINCT FROM OLD.variant
JOIN TranslationTemplateItem AS new_template_item
ON (old_template_item.potemplate =
new_template_item.potemplate)
JOIN TranslationMessage AS new_latest_message
ON new_latest_message.potmsgset =
new_template_item.potmsgset AND
new_latest_message.language = OLD.language AND
new_latest_message.variant IS NOT DISTINCT FROM OLD.variant
LEFT OUTER JOIN POfileTranslator AS ExistingEntry
ON ExistingEntry.person = OLD.submitter AND
ExistingEntry.pofile = POFile.id
WHERE
new_latest_message.submitter = OLD.submitter AND
ExistingEntry IS NULL
ORDER BY new_latest_message.submitter, pofile.id,
new_latest_message.date_created DESC,
new_latest_message.id DESC;
END IF;
-- No NEW with DELETE, so we can short circuit and leave.
IF TG_OP = 'DELETE' THEN
RETURN NULL; -- Ignored because this is an AFTER trigger
END IF;
END IF;
-- Standard 'upsert' loop to avoid race conditions.
LOOP
UPDATE POFileTranslator
SET
date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
latest_message = NEW.id
FROM POFile, TranslationTemplateItem
WHERE person = NEW.submitter AND
TranslationTemplateItem.potmsgset=NEW.potmsgset AND
TranslationTemplateItem.potemplate=pofile.potemplate AND
pofile.language=NEW.language AND
pofile.variant IS NOT DISTINCT FROM NEW.variant AND
POFileTranslator.pofile = pofile.id;
IF found THEN
RETURN NULL; -- Return value ignored as this is an AFTER trigger
END IF;
BEGIN
INSERT INTO POFileTranslator (person, pofile, latest_message)
SELECT DISTINCT ON (NEW.submitter, pofile.id)
NEW.submitter, pofile.id, NEW.id
FROM TranslationTemplateItem
JOIN POFile
ON pofile.language = NEW.language AND
pofile.variant IS NOT DISTINCT FROM NEW.variant AND
pofile.potemplate = translationtemplateitem.potemplate
WHERE
TranslationTemplateItem.potmsgset = NEW.potmsgset;
RETURN NULL; -- Return value ignored as this is an AFTER trigger
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
END LOOP;
END;
Return the id of the row in the NameBlacklist table that matches the given name, or NULL if no regexps in the NameBlacklist table match.
import re
name = args[0].decode("UTF-8")
if not SD.has_key("select_plan"):
SD["select_plan"] = plpy.prepare("""
SELECT id, regexp FROM NameBlacklist ORDER BY id
""")
SD["compiled"] = {}
compiled = SD["compiled"]
for row in plpy.execute(SD["select_plan"]):
regexp_id = row["id"]
regexp_txt = row["regexp"]
if (compiled.get(regexp_id) is None
or compiled[regexp_id][0] != regexp_txt):
regexp = re.compile(
regexp_txt, re.IGNORECASE | re.UNICODE | re.VERBOSE
)
compiled[regexp_id] = (regexp_txt, regexp)
else:
regexp = compiled[regexp_id][1]
if regexp.search(name) is not None:
return regexp_id
return None
Return the number of NULLs in the first row of the given array.
DECLARE
v_index integer;
v_null_count integer := 0;
BEGIN
FOR v_index IN array_lower(p_values,1)..array_upper(p_values,1) LOOP
IF p_values[v_index] IS NULL THEN
v_null_count := v_null_count + 1;
END IF;
END LOOP;
RETURN v_null_count;
END;
Remove any DAG edges leading to/from the deleted package set.
BEGIN
DELETE FROM flatpackagesetinclusion
WHERE parent = OLD.id AND child = OLD.id;
-- A package set was deleted; it may have participated in package set
-- inclusion relations in a sub/superset role; delete all inclusion
-- relationships in which it participated.
DELETE FROM packagesetinclusion
WHERE parent = OLD.id OR child = OLD.id;
RETURN OLD;
END;
Insert self-referencing DAG edge when a new package set is inserted.
BEGIN
-- A new package set was inserted; make it a descendent of itself in
-- the flattened package set inclusion table in order to facilitate
-- querying.
INSERT INTO flatpackagesetinclusion(parent, child)
VALUES (NEW.id, NEW.id);
RETURN NULL;
END;
Maintain the transitive closure in the DAG when an edge leading to/from a package set is deleted.
BEGIN
-- A package set inclusion relationship was deleted i.e. a set M
-- ceases to include another set N as a subset.
-- For an explanation of the queries below please see page 5 of
-- "Maintaining Transitive Closure of Graphs in SQL"
-- http://www.comp.nus.edu.sg/~wongls/psZ/dlsw-ijit97-16.ps
CREATE TEMP TABLE tmp_fpsi_suspect(
parent integer NOT NULL,
child integer NOT NULL);
CREATE TEMP TABLE tmp_fpsi_trusted(
parent integer NOT NULL,
child integer NOT NULL);
CREATE TEMP TABLE tmp_fpsi_good(
parent integer NOT NULL,
child integer NOT NULL);
INSERT INTO tmp_fpsi_suspect (
SELECT X.parent, Y.child
FROM flatpackagesetinclusion X, flatpackagesetinclusion Y
WHERE X.child = OLD.parent AND Y.parent = OLD.child
UNION
SELECT X.parent, OLD.child FROM flatpackagesetinclusion X
WHERE X.child = OLD.parent
UNION
SELECT OLD.parent, X.child FROM flatpackagesetinclusion X
WHERE X.parent = OLD.child
UNION
SELECT OLD.parent, OLD.child
);
INSERT INTO tmp_fpsi_trusted (
SELECT parent, child FROM flatpackagesetinclusion
EXCEPT
SELECT parent, child FROM tmp_fpsi_suspect
UNION
SELECT parent, child FROM packagesetinclusion psi
WHERE psi.parent != OLD.parent AND psi.child != OLD.child
);
INSERT INTO tmp_fpsi_good (
SELECT parent, child FROM tmp_fpsi_trusted
UNION
SELECT T1.parent, T2.child
FROM tmp_fpsi_trusted T1, tmp_fpsi_trusted T2
WHERE T1.child = T2.parent
UNION
SELECT T1.parent, T3.child
FROM tmp_fpsi_trusted T1, tmp_fpsi_trusted T2, tmp_fpsi_trusted T3
WHERE T1.child = T2.parent AND T2.child = T3.parent
);
DELETE FROM flatpackagesetinclusion fpsi
WHERE NOT EXISTS (
SELECT * FROM tmp_fpsi_good T
WHERE T.parent = fpsi.parent AND T.child = fpsi.child);
DROP TABLE tmp_fpsi_good;
DROP TABLE tmp_fpsi_trusted;
DROP TABLE tmp_fpsi_suspect;
RETURN OLD;
END;
Maintain the transitive closure in the DAG for a newly inserted edge leading to/from a package set.
BEGIN
DECLARE
parent_name text;
child_name text;
BEGIN
IF EXISTS(
SELECT * FROM flatpackagesetinclusion
WHERE parent = NEW.child AND child = NEW.parent LIMIT 1)
THEN
SELECT name INTO parent_name FROM packageset WHERE id = NEW.parent;
SELECT name INTO child_name FROM packageset WHERE id = NEW.child;
RAISE EXCEPTION 'Package set % already includes %. Adding (% -> %) would introduce a cycle in the package set graph (DAG).', child_name, parent_name, parent_name, child_name;
END IF;
END;
-- A new package set inclusion relationship was inserted i.e. a set M
-- now includes another set N as a subset.
-- For an explanation of the queries below please see page 4 of
-- "Maintaining Transitive Closure of Graphs in SQL"
-- http://www.comp.nus.edu.sg/~wongls/psZ/dlsw-ijit97-16.ps
CREATE TEMP TABLE tmp_fpsi_new(
parent integer NOT NULL,
child integer NOT NULL);
INSERT INTO tmp_fpsi_new (
SELECT
X.parent AS parent, NEW.child AS child
FROM flatpackagesetinclusion X WHERE X.child = NEW.parent
UNION
SELECT
NEW.parent AS parent, X.child AS child
FROM flatpackagesetinclusion X WHERE X.parent = NEW.child
UNION
SELECT
X.parent AS parent, Y.child AS child
FROM flatpackagesetinclusion X, flatpackagesetinclusion Y
WHERE X.child = NEW.parent AND Y.parent = NEW.child
);
INSERT INTO tmp_fpsi_new(parent, child) VALUES(NEW.parent, NEW.child);
INSERT INTO flatpackagesetinclusion(parent, child) (
SELECT
parent, child FROM tmp_fpsi_new
EXCEPT
SELECT F.parent, F.child FROM flatpackagesetinclusion F
);
DROP TABLE tmp_fpsi_new;
RETURN NULL;
END;
Return a string suitable for sorting people on, generated by stripping noise out of displayname and concatenating name
# NB: If this implementation is changed, the person_sort_idx needs to be
# rebuilt along with any other indexes using it.
import re
try:
strip_re = SD["strip_re"]
except KeyError:
strip_re = re.compile("(?:[^\w\s]|[\d_])", re.U)
SD["strip_re"] = strip_re
displayname, name = args
# Strip noise out of displayname. We do not have to bother with
# name, as we know it is just plain ascii.
displayname = strip_re.sub('', displayname.decode('UTF-8').lower())
return ("%s, %s" % (displayname.strip(), name)).encode('UTF-8')
pgstattuplebyid
pgstattuple
plpython_call_handler
Returns the worst lag time known to this node in our cluster, or NULL if not a replicated installation.
DECLARE
v_lag interval;
BEGIN
SELECT INTO v_lag max(st_lag_time) FROM _sl.sl_status;
RETURN v_lag;
-- Slony-I not installed here - non-replicated setup.
EXCEPTION
WHEN invalid_schema_name THEN
RETURN NULL;
WHEN undefined_table THEN
RETURN NULL;
END;
A sane version number for use by ProductRelease and DistroRelease. We may make it less strict if required, but it would be nice if we can enforce simple version strings because we use them in URLs
import re
if re.search("""^(?ix)
[0-9a-z]
( [0-9a-z] | [0-9a-z.-]*[0-9a-z] )*
$""", args[0]):
return 1
return 0
AFTER INSERT trigger on BugMessage maintaining the Bug.date_last_message column
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE Bug
SET date_last_message = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
WHERE Bug.id = NEW.bug;
ELSE
UPDATE Bug
SET date_last_message = max_datecreated
FROM (
SELECT BugMessage.bug, max(Message.datecreated) AS max_datecreated
FROM BugMessage, Message
WHERE BugMessage.id <> OLD.id
AND BugMessage.bug = OLD.bug
AND BugMessage.message = Message.id
GROUP BY BugMessage.bug
) AS MessageSummary
WHERE Bug.id = MessageSummary.bug;
END IF;
RETURN NULL; -- Ignored - this is an AFTER trigger
END;
AFTER UPDATE trigger on BugAffectsPerson maintaining the Bug.users_affected_count column
BEGIN
IF TG_OP = 'UPDATE' THEN
IF NEW.bug = OLD.bug THEN
RETURN NULL; -- Ignored - this is an AFTER trigger.
END IF;
END IF;
IF TG_OP <> 'DELETE' THEN
UPDATE Bug SET message_count = message_count + 1
WHERE Bug.id = NEW.bug;
END IF;
IF TG_OP <> 'INSERT' THEN
UPDATE Bug SET message_count = message_count - 1
WHERE Bug.id = OLD.bug;
END IF;
RETURN NULL; -- Ignored - this is an AFTER trigger.
END;
AFTER UPDATE trigger on Bug maintaining the Bug.number_of_duplicates column
BEGIN
-- Short circuit on an update that doesn't change duplicateof
IF TG_OP = 'UPDATE' THEN
IF NEW.duplicateof = OLD.duplicateof THEN
RETURN NULL; -- Ignored - this is an AFTER trigger
END IF;
END IF;
-- For update or delete, possibly decrement a bug's dupe count
IF TG_OP <> 'INSERT' THEN
IF OLD.duplicateof IS NOT NULL THEN
UPDATE Bug SET number_of_duplicates = number_of_duplicates - 1
WHERE Bug.id = OLD.duplicateof;
END IF;
END IF;
-- For update or insert, possibly increment a bug's dupe cout
IF TG_OP <> 'DELETE' THEN
IF NEW.duplicateof IS NOT NULL THEN
UPDATE Bug SET number_of_duplicates = number_of_duplicates + 1
WHERE Bug.id = NEW.duplicateof;
END IF;
END IF;
RETURN NULL; -- Ignored - this is an AFTER trigger
END;
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.affected = TRUE THEN
UPDATE Bug
SET users_affected_count = users_affected_count + 1
WHERE Bug.id = NEW.bug;
ELSE
UPDATE Bug
SET users_unaffected_count = users_unaffected_count + 1
WHERE Bug.id = NEW.bug;
END IF;
END IF;
IF TG_OP = 'DELETE' THEN
IF OLD.affected = TRUE THEN
UPDATE Bug
SET users_affected_count = users_affected_count - 1
WHERE Bug.id = OLD.bug;
ELSE
UPDATE Bug
SET users_unaffected_count = users_unaffected_count - 1
WHERE Bug.id = OLD.bug;
END IF;
END IF;
IF TG_OP = 'UPDATE' THEN
IF OLD.affected <> NEW.affected THEN
IF NEW.affected THEN
UPDATE Bug
SET users_affected_count = users_affected_count + 1,
users_unaffected_count = users_unaffected_count - 1
WHERE Bug.id = OLD.bug;
ELSE
UPDATE Bug
SET users_affected_count = users_affected_count - 1,
users_unaffected_count = users_unaffected_count + 1
WHERE Bug.id = OLD.bug;
END IF;
END IF;
END IF;
RETURN NULL;
END;
Update BugTask.date_milestone_set when BugTask.milestone is changed.
BEGIN
IF TG_OP = 'INSERT' THEN
-- If the inserted row as a milestone set, set date_milestone_set.
IF NEW.milestone IS NOT NULL THEN
UPDATE BugTask
SET date_milestone_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
WHERE BugTask.id = NEW.id;
END IF;
END IF;
IF TG_OP = 'UPDATE' THEN
IF OLD.milestone IS NULL THEN
-- If there was no milestone set, check if the new row has a
-- milestone set and set date_milestone_set.
IF NEW.milestone IS NOT NULL THEN
UPDATE BugTask
SET date_milestone_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
WHERE BugTask.id = NEW.id;
END IF;
ELSE
IF NEW.milestone IS NULL THEN
-- If the milestone was unset, clear date_milestone_set.
UPDATE BugTask
SET date_milestone_set = NULL
WHERE BugTask.id = NEW.id;
ELSE
-- Update date_milestone_set if the bug task was
-- targeted to another milestone.
IF NEW.milestone != OLD.milestone THEN
UPDATE BugTask
SET date_milestone_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
WHERE BugTask.id = NEW.id;
END IF;
END IF;
END IF;
END IF;
RETURN NULL; -- Ignored - this is an AFTER trigger.
END;
BEFORE UPDATE trigger on Account that maintains the Account.date_status_set column.
BEGIN
IF OLD.status <> NEW.status THEN
NEW.date_status_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
END IF;
RETURN NEW;
END;
# If someone is trying to explicitly set the openid_identifier, let them.
# This also causes openid_identifiers to be left alone if this is an
# UPDATE trigger.
if TD['new']['openid_identifier'] is not None:
return None
from random import choice
# Non display confusing characters
chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'
# character length of tokens. Can be increased, decreased or even made
# random - Launchpad does not care. 7 means it takes 40 bytes to store
# a null-terminated Launchpad identity URL on the current domain name.
length=7
loop_count = 0
while loop_count < 20000:
# Generate a random openid_identifier
oid = ''.join(choice(chars) for count in range(length))
# Check if the oid is already in the db, although this is pretty
# unlikely
rv = plpy.execute("""
SELECT COUNT(*) AS num FROM Person WHERE openid_identifier = '%s'
""" % oid, 1)
if rv[0]['num'] == 0:
TD['new']['openid_identifier'] = oid
return "MODIFY"
loop_count += 1
if loop_count == 1:
plpy.warning(
'Clash generating unique openid_identifier. '
'Increase length if you see this warning too much.')
plpy.error(
"Unable to generate unique openid_identifier. "
"Need to increase length of tokens.")
Store a normalized concatenation of the request's address into the normalized_address column.
BEGIN
NEW.normalized_address =
lower(
-- Strip off everything that's not alphanumeric
-- characters.
regexp_replace(
coalesce(NEW.addressline1, '') || ' ' ||
coalesce(NEW.addressline2, '') || ' ' ||
coalesce(NEW.city, ''),
'[^a-zA-Z0-9]+', '', 'g'));
RETURN NEW;
END;
Return the SHA1 one way cryptographic hash as a string of 40 hex digits
import sha
return sha.new(args[0]).hexdigest()
Return the lower case version of a UTF-8 encoded string.
return args[0].decode('utf8').lower().encode('utf8')
Maintain the cached name columns in Branch.
DECLARE
needs_update boolean := FALSE;
BEGIN
IF TG_OP = 'INSERT' THEN
needs_update := TRUE;
ELSIF (NEW.owner_name IS NULL
OR NEW.unique_name IS NULL
OR OLD.owner_name <> NEW.owner_name
OR OLD.unique_name <> NEW.unique_name
OR (NEW.target_suffix IS NULL <> OLD.target_suffix IS NULL)
OR COALESCE(OLD.target_suffix, '') <> COALESCE(NEW.target_suffix, '')
OR OLD.name <> NEW.name
OR OLD.owner <> NEW.owner
OR COALESCE(OLD.product, -1) <> COALESCE(NEW.product, -1)
OR COALESCE(OLD.distroseries, -1) <> COALESCE(NEW.distroseries, -1)
OR COALESCE(OLD.sourcepackagename, -1)
<> COALESCE(NEW.sourcepackagename, -1)) THEN
needs_update := TRUE;
END IF;
IF needs_update THEN
SELECT
Person.name AS owner_name,
COALESCE(Product.name, SPN.name) AS target_suffix,
'~' || Person.name || '/' || COALESCE(
Product.name,
Distribution.name || '/' || Distroseries.name
|| '/' || SPN.name,
'+junk') || '/' || NEW.name AS unique_name
INTO NEW.owner_name, NEW.target_suffix, NEW.unique_name
FROM Person
LEFT OUTER JOIN DistroSeries ON NEW.distroseries = DistroSeries.id
LEFT OUTER JOIN Product ON NEW.product = Product.id
LEFT OUTER JOIN Distribution
ON Distroseries.distribution = Distribution.id
LEFT OUTER JOIN SourcepackageName AS SPN
ON SPN.id = NEW.sourcepackagename
WHERE Person.id = NEW.owner;
END IF;
RETURN NEW;
END;
Ensure the given test is a valid absolute URL, containing both protocol and network location
from urlparse import urlparse
(scheme, netloc, path, params, query, fragment) = urlparse(args[0])
# urlparse in the stdlib does not correctly parse the netloc from
# sftp and bzr+ssh schemes, so we have to manually check those
if scheme in ("sftp", "bzr+ssh"):
return 1
if not (scheme and netloc):
return 0
return 1
validate a branch name. As per valid_name, except we allow uppercase and @
import re
name = args[0]
pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z"
if re.match(pat, name):
return 1
return 0
validate a bug name As per valid_name, except numeric-only names are not allowed (including names that look like floats).
import re
name = args[0]
pat = r"^[a-z][a-z0-9+\.\-]+$"
if re.match(pat, name):
return 1
return 0
validate a common vulnerability number as defined on www.cve.mitre.org, minus the CAN- or CVE- prefix.
import re
name = args[0]
pat = r"^(19|20)\d{2}-\d{4}$"
if re.match(pat, name):
return 1
return 0
validate a version number as per Debian Policy
import re
m = re.search("""^(?ix)
([0-9]+:)?
([0-9a-z][a-z0-9+:.~-]*?)
(-[a-z0-9+.~]+)?
$""", args[0])
if m is None:
return 0
epoch, version, revision = m.groups()
if not epoch:
# Can''t contain : if no epoch
if ":" in version:
return 0
if not revision:
# Can''t contain - if no revision
if "-" in version:
return 0
return 1
Returns true if passed a valid GPG fingerprint. Valid GPG fingerprints are a 40 character long hexadecimal number in uppercase.
import re
if re.match(r"[\dA-F]{40}", args[0]) is not None:
return 1
else:
return 0
Returns true if passed a valid GPG keyid. Valid GPG keyids are an 8 character long hexadecimal number in uppercase (in reality, they are 16 characters long but we are using the 'common' definition.
import re
if re.match(r"[\dA-F]{8}", args[0]) is not None:
return 1
else:
return 0
validate a name. Names must contain only lowercase letters, numbers, ., & -. They must start with an alphanumeric. They are ASCII only. Names are useful for mneumonic identifiers such as nicknames and as URL components. This specification is the same as the Debian product naming policy. Note that a valid name might be all integers, so there is a possible namespace conflict if URL traversal is possible by name as well as id.
import re
name = args[0]
pat = r"^[a-z0-9][a-z0-9\+\.\-]*\Z"
if re.match(pat, name):
return 1
return 0
Returns true if the input can be compiled as a regular expression.
import re
try:
re.compile(args[0])
except:
return False
else:
return True
Trigger function to ensure that every row added to the Person table gets a corresponding row in the TeamParticipation table, as per the TeamParticipationUsage page on the Launchpad wiki
BEGIN
INSERT INTO TeamParticipation (person, team)
VALUES (NEW.id, NEW.id);
RETURN NULL;
END;
import re
# I think this method would be more robust if we used a real
# tokenizer and parser to generate the query string, but we need
# something suitable for use as a stored procedure which currently
# means no external dependancies.
# Convert to Unicode
query = args[0].decode('utf8')
## plpy.debug('1 query is %s' % repr(query))
# Normalize whitespace
query = re.sub("(?u)\s+"," ", query)
# Convert AND, OR, NOT and - to tsearch2 punctuation
query = re.sub(r"(?u)(?:^|\s)-([\w\(])", r" !\1", query)
query = re.sub(r"(?u)\bAND\b", "&", query)
query = re.sub(r"(?u)\bOR\b", "|", query)
query = re.sub(r"(?u)\bNOT\b", " !", query)
## plpy.debug('2 query is %s' % repr(query))
# Deal with unwanted punctuation. We convert strings of punctuation
# inside words to a '-' character for the hypenation handling below
# to deal with further. Outside of words we replace with whitespace.
# We don't mess with -&|!()' as they are handled later.
#punctuation = re.escape(r'`~@#$%^*+=[]{}:;"<>,.?\/')
punctuation = r"[^\w\s\-\&\|\!\(\)']"
query = re.sub(r"(?u)(\w)%s+(\w)" % (punctuation,), r"\1-\2", query)
query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
## plpy.debug('3 query is %s' % repr(query))
# Strip ! characters inside and at the end of a word
query = re.sub(r"(?u)(?<=\w)[\!]+", " ", query)
# Now that we have handle case sensitive booleans, convert to lowercase
query = query.lower()
# Convert foo-bar to ((foo&bar)|foobar) and foo-bar-baz to
# ((foo&bar&baz)|foobarbaz)
def hyphen_repl(match):
bits = match.group(0).split("-")
return "((%s)|%s)" % ("&".join(bits), "".join(bits))
query = re.sub(r"(?u)\b\w+-[\w\-]+\b", hyphen_repl, query)
## plpy.debug('4 query is %s' % repr(query))
# Any remaining - characters are spurious
query = query.replace('-','')
# Remove unpartnered bracket on the left and right
query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
# Remove spurious brackets
query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
## plpy.debug('5 query is %s' % repr(query))
# Insert & between tokens without an existing boolean operator
# ( not proceeded by (|&!
query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
## plpy.debug('6 query is %s' % repr(query))
# ) not followed by )|&
query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
## plpy.debug('6.1 query is %s' % repr(query))
# Whitespace not proceded by (|&! not followed by &|
query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
## plpy.debug('7 query is %s' % repr(query))
# Detect and repair syntax errors - we are lenient because
# this input is generally from users.
# Fix unbalanced brackets
openings = query.count("(")
closings = query.count(")")
if openings > closings:
query = query + " ) "*(openings-closings)
elif closings > openings:
query = " ( "*(closings-openings) + query
## plpy.debug('8 query is %s' % repr(query))
# Strip ' character that do not have letters on both sides
query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
# Brackets containing nothing but whitespace and booleans, recursive
last = ""
while last != query:
last = query
query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
## plpy.debug('9 query is %s' % repr(query))
# An & or | following a (
query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
## plpy.debug('10 query is %s' % repr(query))
# An &, | or ! immediatly before a )
query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
## plpy.debug('11 query is %s' % repr(query))
# An &,| or ! followed by another boolean.
query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
## plpy.debug('12 query is %s' % repr(query))
# Leading & or |
query = re.sub(r"(?u)^[\s\&\|]+", "", query)
## plpy.debug('13 query is %s' % repr(query))
# Trailing &, | or !
query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
## plpy.debug('14 query is %s' % repr(query))
# If we have nothing but whitespace and tsearch2 operators,
# return NULL.
if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
return None
# Convert back to UTF-8
query = query.encode('utf8')
## plpy.debug('15 query is %s' % repr(query))
return query or None
select prsname::text from pg_catalog.pg_ts_parser p join pg_ts_config c on cfgparser = p.oid where c.oid = show_curcfg();
tsvector_concat
tsa_dex_init
tsa_dex_lexize
Trigger function that keeps the fti tsvector column up to date.
new = TD["new"]
args = TD["args"][:]
# Short circuit if none of the relevant columns have been
# modified and fti is not being set to NULL (setting the fti
# column to NULL is thus how we can force a rebuild of the fti
# column).
if TD["event"] == "UPDATE" and new["fti"] != None:
old = TD["old"]
relevant_modification = False
for column_name in args[::2]:
if new[column_name] != old[column_name]:
relevant_modification = True
break
if not relevant_modification:
return "OK"
# Generate an SQL statement that turns the requested
# column values into a weighted tsvector
sql = []
for i in range(0, len(args), 2):
sql.append(
"ts2.setweight(ts2.to_tsvector('default', coalesce("
"substring(ltrim($%d) from 1 for 2500),'')),"
"CAST($%d AS \"char\"))" % (i + 1, i + 2))
args[i] = new[args[i]]
sql = "SELECT %s AS fti" % "||".join(sql)
# Execute and store in the fti column
plan = plpy.prepare(sql, ["text", "char"] * (len(args)/2))
new["fti"] = plpy.execute(plan, args, 1)[0]["fti"]
# Tell PostgreSQL we have modified the data
return "MODIFY"
Convert a string to an unparsed tsearch2 query
import re
# I think this method would be more robust if we used a real
# tokenizer and parser to generate the query string, but we need
# something suitable for use as a stored procedure which currently
# means no external dependancies.
# Convert to Unicode
query = args[0].decode('utf8')
## plpy.debug('1 query is %s' % repr(query))
# Normalize whitespace
query = re.sub("(?u)\s+"," ", query)
# Convert AND, OR, NOT and - to tsearch2 punctuation
query = re.sub(r"(?u)(?:^|\s)-([\w\(])", r" !\1", query)
query = re.sub(r"(?u)\bAND\b", "&", query)
query = re.sub(r"(?u)\bOR\b", "|", query)
query = re.sub(r"(?u)\bNOT\b", " !", query)
## plpy.debug('2 query is %s' % repr(query))
# Deal with unwanted punctuation. We convert strings of punctuation
# inside words to a '-' character for the hypenation handling below
# to deal with further. Outside of words we replace with whitespace.
# We don't mess with -&|!()' as they are handled later.
#punctuation = re.escape(r'`~@#$%^*+=[]{}:;"<>,.?\/')
punctuation = r"[^\w\s\-\&\|\!\(\)']"
query = re.sub(r"(?u)(\w)%s+(\w)" % (punctuation,), r"\1-\2", query)
query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
## plpy.debug('3 query is %s' % repr(query))
# Strip ! characters inside and at the end of a word
query = re.sub(r"(?u)(?<=\w)[\!]+", " ", query)
# Now that we have handle case sensitive booleans, convert to lowercase
query = query.lower()
# Convert foo-bar to ((foo&bar)|foobar) and foo-bar-baz to
# ((foo&bar&baz)|foobarbaz)
def hyphen_repl(match):
bits = match.group(0).split("-")
return "((%s)|%s)" % ("&".join(bits), "".join(bits))
query = re.sub(r"(?u)\b\w+-[\w\-]+\b", hyphen_repl, query)
## plpy.debug('4 query is %s' % repr(query))
# Any remaining - characters are spurious
query = query.replace('-','')
# Remove unpartnered bracket on the left and right
query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
# Remove spurious brackets
query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
## plpy.debug('5 query is %s' % repr(query))
# Insert & between tokens without an existing boolean operator
# ( not proceeded by (|&!
query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
## plpy.debug('6 query is %s' % repr(query))
# ) not followed by )|&
query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
## plpy.debug('6.1 query is %s' % repr(query))
# Whitespace not proceded by (|&! not followed by &|
query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
## plpy.debug('7 query is %s' % repr(query))
# Detect and repair syntax errors - we are lenient because
# this input is generally from users.
# Fix unbalanced brackets
openings = query.count("(")
closings = query.count(")")
if openings > closings:
query = query + " ) "*(openings-closings)
elif closings > openings:
query = " ( "*(closings-openings) + query
## plpy.debug('8 query is %s' % repr(query))
# Strip ' character that do not have letters on both sides
query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
# Brackets containing nothing but whitespace and booleans, recursive
last = ""
while last != query:
last = query
query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
## plpy.debug('9 query is %s' % repr(query))
# An & or | following a (
query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
## plpy.debug('10 query is %s' % repr(query))
# An &, | or ! immediatly before a )
query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
## plpy.debug('11 query is %s' % repr(query))
# An &,| or ! followed by another boolean.
query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
## plpy.debug('12 query is %s' % repr(query))
# Leading & or |
query = re.sub(r"(?u)^[\s\&\|]+", "", query)
## plpy.debug('13 query is %s' % repr(query))
# Trailing &, | or !
query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
## plpy.debug('14 query is %s' % repr(query))
# If we have nothing but whitespace and tsearch2 operators,
# return NULL.
if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
return None
# Convert back to UTF-8
query = query.encode('utf8')
## plpy.debug('15 query is %s' % repr(query))
p = plpy.prepare("SELECT to_tsquery('default', $1) AS x", ["text"])
query = plpy.execute(p, [query], 1)[0]["x"]
return query or None
tsa_get_covers
ts_headline_byid
ts_headline_byid_opt
tsa_headline_byname
tsa_headline_byname
ts_headline
ts_headline_opt
tsvector_length
ts_lexize
tsa_lexize_bycurrent
tsa_lexize_byname
tsquery_numnode
ts_parse_byid
tsa_parse_current
ts_parse_byname
plainto_tsquery_byid
plainto_tsquery
tsa_plainto_tsquery_name
tsa_prsd_end
tsa_prsd_getlexeme
tsa_prsd_headline
tsa_prsd_lextype
tsa_prsd_start
tsquerytree
ts_rank_wtt
ts_rank_wttf
ts_rank_tt
ts_rank_ttf
ts_rankcd_wtt
ts_rankcd_wttf
ts_rankcd_tt
ts_rankcd_ttf
tsa_reset_tsearch
tsquery_rewrite_query
tsquery_rewrite
aggregate_dummy
tsa_rewrite_accum
tsa_rewrite_finish
tsa_set_curcfg
tsa_set_curcfg_byname
tsa_set_curdict
tsa_set_curdict_byname
tsa_set_curprs
tsa_set_curprs_byname
tsvector_setweight
get_current_ts_config
tsa_snb_en_init
tsa_snb_lexize
tsa_snb_ru_init
tsa_snb_ru_init_koi8
tsa_snb_ru_init_utf8
tsa_spell_init
tsa_spell_lexize
ts_stat1
ts_stat2
tsvector_strip
tsa_syn_init
tsa_syn_lexize
tsa_thesaurus_init
tsa_thesaurus_lexize
to_tsquery_byid
to_tsquery
tsa_to_tsquery_name
to_tsvector_byid
to_tsvector
tsa_to_tsvector_name
tsa_token_type_current
ts_token_type_byid
ts_token_type_byname
select
(select c.cfgname::text from pg_catalog.pg_ts_config as c
where c.oid = show_curcfg()),
t.alias as tok_type,
t.descr as description,
p.token,
ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary::pg_catalog.text
FROM pg_catalog.pg_ts_config_map AS m
WHERE m.mapcfg = show_curcfg() AND m.maptokentype = p.tokid
ORDER BY m.mapseqno )
AS dict_name,
strip(to_tsvector(p.token)) as tsvector
from
parse( _get_parser_from_curcfg(), $1 ) as p,
token_type() as t
where
t.tokid = p.tokid
tsa_tsearch2
tsq_mcontained
tsq_mcontains
tsquery_and
tsquery_not
tsquery_or
Generated by PostgreSQL Autodoc