~abrody/+junk/launchpad-dbpatches

Database Patch Numbers

======================

The following database patch numbers have been allocated for use. These

patches may not have been deployed or landed yet.

Comment should be meaningful enough for the DBA to determine if a .sql

script claiming to be a particular patch really is a particular patch.

Feel free to reuse numbers marked as moved. Patches normally only get

renumbered if they are to be applied live.

Notes about patches

-------------------

 - Do not include BEGIN or COMMIT. They might be useful when

   you are testing, but don't let them land with these statements.

 - COMMENTs go in comments.sql.

 - You need COMMENTs for all new tables and columns. Fix COMMENTs

   when you are changing the meaning of a column or table.

 - Don't use TRUNCATE. It breaks replication.

 - Don't use DROP TABLE. It breaks replication. Instead, remove

   any FOREIGN KEY constraints and do `ALTER TABLE Foo SET SCHEMA todrop;`.

   The table will be removed from replication and dropped later by our

   update scripts.

 - Some database patches may be applied live. This needs to be determined

   on a case-by-case basis by the DBA. If your patch needs to be applied

   live, things it cannot do include:

    - ALTER TABLE of any kind.

    - Creating a new table that REFERENCES a busy table (such as Person).

Notes about patch numbers

-------------------------

 - Unapplied patches are applied in sequence. Patches can only depend

   on earlier numbered patches.

 - We refer to the three fields in the patch number as major, minor

   and patch.

   - 'major' changes when we create a new database baseline in the tree.

     The baseline is launchpad-$major-00-0.sql, and is regenerated by the

     DBA from a production database dump to remove any skew between

     production and development. Any db patches not yet deployed need

     to be renumbered to match the new baseline, and deployed patches

     removed or archived.

   - 'minor' and 'patch' are unique to your database patch.

     - Minor number can be reused from any of the previously allocated

       patch numbers (as long as the dependency graph is satisfied).

     - A 'patch' or 0 means the patch can only be deployed as part of a

       full update (database and code). On startup, our systems assert

       that the set of -0 database patches in the local source tree and

       the set of -0 patches that have been applied to the database are

       the same. This is a safety net to ensure systems do not attempt

       to run against an unexpected database schema.

     - /!\ UPDATE: With fastdowntime deploys, we will rarely if ever

       use -0 patches.

     - A non-zero 'patch' means the database patch can be applied at

       any time without affecting any production code. The runtime

       checks used for -0 patches are not applied. It is your

       responsibility to ensure your patch does not break deployed code

       or trunk.

     - A non-zero 'patch' *may* be deployable live by the DBA. They need

       to be analyzed on a case by case basis, potentially causing them

       to be split into a number of smaller patches.

Allocated Database Patch Numbers

--------------------------------

========= ============= =====================================================

 Patch #  Who Allocated Comment

========= ============= =====================================================

2209-00-0 stub          Dummy patch sacrified to patch application machinery

2209-00-1 jtv           NOT NULL on [BS]PPH.{binary,source}packagename.

2209-00-2 jelmer        ???

2209-00-3 frankban      Add milestonetag table.

2209-00-4 gmb           Add constraints to Person and EmailAddress.

2209-00-5 stub          PG 9.1 compatibility fixes

2209-00-6 wgrant        Index ProductReleaseFile.productrelease and Packaging.productseries

2209-00-7 stub          Explicit statistics sample size on TeamMembership

2209-00-8 wgrant        Include DEBUG in archive__distribution__purpose__key

2209-00-9 wgrant        scriptactivity and bugbranch indices

2209-01-0 wgrant        Make ValidPerson(OrTeam)Cache use EmailAddress.person

2209-01-1 wgrant        Drop EmailAddress.account

2209-02-0 Laney         SPPH.sponsor

2209-04-0 Ursinha       Specification.{date_last_changed,last_changed_by}

2209-05-1 StevenK       New index on BPPH.

2209-06-1 salgado       Add SpecificationWorkItem and SpecificationWorkItemChange tables

2209-07-0 wgrant        Drop BugTask.fti

2209-07-1 wgrant        Fix calculate_bug_heat to not age.

2209-08-1 wallyworld    Drop private_bugs_need_contact constraint on product table.

2209-09-0 StevenK       Destroy Entitlement

2209-10-0 StevenK       Wide-scale index cleanup.

2209-11-0 wgrant        Drop old access policy person and branch foreign keys.

2209-11-1 wgrant        Remodel access policy schema

2209-11-2 wgrant        Drop foreign keys between the old schema so it can drop. Oops.

2209-11-3 wgrant        Triggers to mirror bug legacy access to sharing.

2209-11-4 wgrant        Rework triggers from 2209-11-3 to use information_type.

2209-11-5 wgrant        Add person and branch foreign keys to new access policy schema.

2209-12-0 StevenK       Add information visibility policy to bug/branch.

2209-12-1 StevenK       Index for bug.information_type.

2209-12-2 StevenK       Make bug.information_type NOT NULL.

2209-12-3 StevenK       Fiddle bugsummary triggers to use information_type.

2209-12-4 StevenK       Index for branch.information_type.

2209-12-5 StevenK       Make branch.information_type NOT NULL.

2209-13-0 lifeless      New emailjob table.

2209-14-0 lifeless      Drop bugjob.

2209-15-0 StevenK       Add SPPH.packageupload.

2209-15-1 sinzui        Add productjob

2209-15-2 stub          Bug #920926 and misc cleanup

2209-15-3 StevenK       Index for SPPH.packageupload

2209-16-0 wgrant        BugTaskFlat

2209-16-1 wgrant        More BugTaskFlat indices

2209-16-2 wgrant        SourcePackagePublishingHistory index for bug component search

2209-16-3 wgrant        date_closed and latest_patch_uploaded onto BugTaskFlat

2209-16-4 wgrant        date_closed and latest_patch_uploaded indices for BugTaskFlat

2209-16-5 wgrant        Drop obsolete Bug and BugTask columns

2209-16-6 StevenK       Transition Branch to new access model, dropping the old one.

2209-16-7 wgrant        Drop the bug legacy access triggers.

2209-16-8 StevenK       Delete obsolete branch function.

2209-17-0 wallyworld    Sharing jobs

2209-17-1 jtv           Drop POFileTranslator.latest_message.

2209-18-0 cjwatson      Add packageset.score.

2209-18-1 cjwatson      Add ArchivePermission.pocket.

2209-18-2 StevenK       Builder.description DROP NOT NULL.

2209-18-3 StevenK       Drop Builder.description.

2209-18-4 cjwatson      Rename packageset.score to relative_build_score.

2209-19-0 wgrant        BugSummary v2

2209-19-1 wgrant        BugSummary v2 -- new search indices

2209-19-2 wgrant        BugSummary v2 -- new unique index

2209-19-3 wgrant        BugSummary v2 -- set access_policy, drop old columns and indices

2209-20-0 stub          Drop SpecificationFeedback foreign keys (person merge)

2209-20-1 danilo        Drop SpecificationFeedback table

2209-21-0 stub          Create GIN index on BugTaskFlat.fti

2209-21-1 stub          Drop GiST index on BugTaskFlat.fti

2209-21-2 stub          Create remaining GIN indexes to replace GiST indexes.

2209-21-3 stub          Drop remaining GiST indexes.

2209-21-4 stub          update_disk_utilization fix for Bug #1007333

2209-22-0 jml           Archive.suppress_subscription_notifications lp:#1006295

2209-22-1 jml           New distribution col - who can create private PPAs

2209-23-0 wgrant        DistributionSourcePackageCache indices

2209-23-1 wgrant        BinaryPackageName and SourcePackageName indices

2209-23-2 wgrant        AccessArtifactGrant and AccessPolicyGrant indices

2209-23-3 wgrant        BinaryPackagePublishingHistory index

2209-23-4 wgrant        Specification(milestone) index

2209-23-5 wgrant        BuildFarmJob(status, id) index

2209-24-1 adeuring      Fix stored procedures ftq() and _ftq()

2209-24-2 stub          Drop obsolete and shadowed fti helpers in the ts2 schema

2209-24-3 adeuring      another fix of ftq() and _ftq()

2209-25-1 Laney         Update -propsed to not be automatic lp:#1016776

2209-26-0 wgrant        Product columns to replace BranchVisibilityPolicy

2209-26-1 wgrant        Drop BranchVisibilityPolicy

2209-26-2 StevenK       Drop BugSubscriptionFilter{Status,Importance}.id

2209-26-3 StevenK       BugSubscriptionFilterInformationType

2209-26-4 StevenK       Drop {Product,Distribution}.security_contact

2209-26-5 StevenK       Drop Product.private_bugs

2209-27-1 cjwatson      support implementation of per-pocket queue admin permissions

2209-27-2 cjwatson      ProcessAcceptedBugsJob

2209-27-3 StevenK       New index on BuildFarmJob.

2209-28-1 abentley      Add information_type to Specification

2209-28-2 abentley      Add index on Specification.information_type

2209-28-3 abentley      Set NOT NULL on Specification.information_type

2209-28-4 adeuring      New column AccessArtifact.specification

2209-28-5 adeuring      Index for AccessArtifact.specification

2209-28-6 StevenK       Create Specification.access_{policy,grants}

2209-29-0 wallyworld    Update functions to account for new embargoed information type

2209-30-1 wallyworld    Alter AccessPolicy table to add person column and associated contraints and index

2209-31-1 deryck        Add specification_sharing_policy to Product.

2209-31-2 deryck        Set default for Product.specification_sharing_policy.

2209-31-3 deryck        Make Product.specification_sharing_policy NOT NULL.

2209-32-0 wgrant        Fix bug_summary_dec typo to handle access policies correctly (bug #1046713)

2209-33-1 abentley      Drop (unused) Specification.private

2209-34-1 jcsackett     Drop (now unused) Product.date_next_suggest_packaging

2209-35-1 deryck        Add information_type to Product.

2209-35-2 deryck        Add index for Product.information_type.

2209-35-3 deryck        Make Product.information_type NOT NULL.

2209-36-0 cjwatson      Flag to redirect Ubuntu release uploads to another pocket

2209-36-1 cjwatson      BinaryPackagePublishingHistory.phased_update_percentage

2209-37-0 StevenK       Drop potmsgset.potemplate.

2209-38-0 wallyworld    Add new LatestPersonSourcePackageReleaseCache table.

2209-38-1 wallyworld    Add creator and maintainer person FK's to LatestPersonSourcePackageReleaseCache table

2209-38-2 wallyworld    Add (archive, distroseries, sourcepackagename) index to LPSPRC table.

2209-38-3 wgrant        LatestPersonSourcePackageReleaseCache indices to satisfy page queries

2209-39-0 stub          Convert contrib to extensions

2209-39-1 stub          Drop LibraryFileAlias.last_accessed

2209-40-0 StevenK       New searchable_names column on PackageUpload

2209-40-1 StevenK       New searchable_versions column on PackageUpload

2209-40-2 StevenK       Indices for PackageUpload.searchable_{name,version}s

2209-40-3 StevenK       Set PackageUpload.searchable_{name,version}s NOT NULL.

2209-41-0 wgrant        BuildFarmJob flattening -- new columns

2209-41-1 wgrant        BuildFarmJob flattening -- LFA indices on new columns

2209-41-2 wgrant        BuildFarmJob flattening -- fix TTB defaults

2209-41-3 wgrant        BuildFarmJob flattening -- search indices

2209-41-4 wgrant        BuildFarmJob flattening -- more search indices

2209-41-5 wgrant        BuildFarmJob flattening -- cleanup

2209-42-0 wgrant        Fix LaunchpadDatabaseRevision.end_time to default to clock_timestamp

2209-43-0 StevenK       Trigram indices for Branch.{,unique_}name.

2209-44-0 StevenK       New columns, PreviewDiff.{branch_merge_proposal,date_created}.

2209-44-1 StevenK       Index for PreviewDiff.{branch_merge_proposal,date_created}.

2209-44-2 StevenK       DROP COLUMN BranchMergeProposal.merge_diff.

2209-44-3 StevenK       PreviewDiff.{branch_merge_proposal,date_created} NOT NULL.

2209-44-4 StevenK       Indices for {Incremental,Preview}Diff.diff.

2209-45-0 wgrant        Archive.publish_debug_symbols

2209-45-1 wgrant        Archive.publish_debug_symbols NOT NULL

2209-46-0 StevenK       Archive.permit_obsolete_series_uploads

2209-47-0 StevenK       Job.json_data

2209-47-1 StevenK       PackageDiff.requester DROP NOT NULL

2209-48-0 cjwatson      Distribution.development_series_alias

2209-49-0 StevenK       ArchiveArch.processor, DistroArchSeries.processor, Processor.restricted

2209-49-1 StevenK       Columns in 49-0 NOT NULL

2209-49-2 StevenK       DROP TABLE ProcessorFamily

2209-50-0 cjwatson      Builder.version

2209-51-0 wgrant        BuildFarmJobOld destruction -- new BuildQueue columns

2209-51-1 wgrant        BuildFarmJobOld destruction -- drop BQ/BPJ/SPRBJ->Job FKs

2209-51-2 wgrant        BuildFarmJobOld destruction -- remove old columns and tables

2209-52-0 wgrant        Introduce BuilderProcessor

2209-52-1 wgrant        Drop Builder.processor

2209-53-0 wgrant        Index SPPH (datecreated, id) for Distribution:+ppas

2209-53-1 wgrant        Replay of new 2209-00-5 for PostgreSQL 9.3 support in activity()

2209-53-2 wgrant        Add Builder.satellite

2209-53-3 wgrant        SPPH.packageupload and BPPH.supersededby FKs for architecture cleaning performance

2209-53-4 wgrant        Drop alllocks and exclusivelocks views; their use of procpid crashes pg_upgrade to 9.3

2209-53-5 wgrant        xPPH(archive, Xpackagename, status) indices for guessPublishedSourcePackageName.

2209-53-6 wgrant        Index BuildQueue(status, virtualized, processor, lastscore DESC, id) for quick candidate queries

2209-53-7 wgrant        Distribution.official_soyuz, enable_ppas, enable_mirrors

2209-53-8 cjwatson      Index xPPH(archive, status) WHERE scheduleddeletiondate IS NULL

2209-53-9 cjwatson      Index BPPH(archive, datecreated, id) for Archive.getAllPublishedBinaries

2209-54-0 cprov         Add CodeReviewInlineComment[,Draft]

2209-55-0 cprov         Update valid_cve() to cope with CVE format changes

2209-56-0 cjwatson      Add LiveFS, LiveFSBuild, and LiveFSFile

2209-56-1 cjwatson      Add NULLS LAST to LiveFS.*builds index

2209-56-2 cjwatson      LiveFSBuild.version

2209-56-3 cjwatson      LiveFS.relative_build_score

2209-56-4 wgrant        Index LiveFSFile(libraryfile)

2209-57-0 wgrant        Virtual builder reset protocol 2.0

2209-58-0 stub          64bit LFC.id - new LFC._id column + sync trigger

2209-58-1 stub          64bit LFC.id - Unique LFC._id

2209-58-2 stub          64bit LFC.id - new LFA._content column + sync trigger

2209-58-3 stub          64bit LFC.id - Index on LFA._content

2209-58-4 stub          64bit LFC.id - Finalization

2209-59-0 wgrant        BinaryPackageBuild.arch_indep

2209-59-1 wgrant        BinaryPackageBuild.arch_indep population (done by garbo on prod)

2209-59-2 wgrant        BinaryPackageBuild.arch_indep NOT NULL

2209-60-0 cjwatson      Sprint.is_physical

2209-61-0 cjwatson      GitRepository, GitRef

2209-61-1 cjwatson      Drop GitRepository.target_implies_owner constraint

2209-61-2 cjwatson      GitRepository.description, flesh out GitRef, GitJob

2209-61-3 blr           GitRepository.reviewer

2209-61-4 cjwatson      GitSubscription

2209-61-5 cjwatson      Add Git columns to BranchMergeProposal

2209-61-6 cjwatson      GitJob.repository DROP NOT NULL

2209-61-7 cjwatson      GitRepository.default_branch

2209-61-8 wgrant        GitRepository indices

2209-61-9 cjwatson      GitSubscription.paths

2209-62-0 wgrant        BuildQueue(status, lastscore DESC, id) for _findBuildCandidate

2209-62-1 wgrant        Translation search and suggestion denormalisation

2209-63-0 stub          Database stat monitoring, dbr schema.

2209-64-0 wgrant        Processor.{supports_{non,}virtualized,build_by_default}

2209-64-1 wgrant        Backfill implicit ArchiveArch rows

2209-64-2 wgrant        Drop DistroArchSeries.supports_virtualized

2209-65-0 blr           Product.vcs_default

2209-66-0 wgrant        Webhook model basics

2209-66-1 cjwatson      Webhook.branch

2209-67-0 wgrant        Drop a dozen unused tables

2209-67-1 wgrant        Add Product.access_policies and index AccessPolicyGrantFlat(grantee, policy)

2209-67-2 wgrant        Backfill Product.access_policies

2209-67-3 wgrant        Index latest Questions, Specifications and Products

2209-68-0 cjwatson      PersonSettings.expanded_notification_footers

2209-68-1 cjwatson      PersonSettings.expanded_notification_footers NOT NULL

2209-68-2 cjwatson      PersonSettings.require_strong_email_authentication

2209-69-0 cjwatson      Add Snap, SnapBuild, and SnapFile

2209-69-1 cprov         Add Snap.private

2209-69-2 cjwatson      Webhook.snap

2209-69-3 cjwatson      Snap store uploading

2209-69-4 cjwatson      Snap auto-building

2209-69-5 cjwatson      Snap.store_channels

2209-69-6 cjwatson      SnappyDistroSeries.preferred

2209-69-7 cjwatson      Snap.git_repository_url

2209-69-8 cjwatson      SnapBuild.revision_id

2209-69-9 cjwatson      Index SnapFile(libraryfile)

2209-70-0 wgrant        XRef

2209-71-0 cjwatson      DistroSeries.publishing_options

2209-71-1 cjwatson      Archive.dirty_suites

2209-72-0 cjwatson      BinaryPackageBuild.external_dependencies

2209-73-0 cjwatson      Git recipes

2209-73-1 cjwatson      Add NULLS LAST to SourcePackageRecipe.*builds indexes

2209-74-0 cjwatson      ArchiveFile

2209-74-1 cjwatson      ArchiveFile.date_created, ArchiveFile.date_superseded

2209-75-0 wgrant        GPGKey foreign key removal new columns

2209-75-1 wgrant        GPGKey foreign key removal new indexes

2209-75-2 wgrant        GPGKey foreign key removal

2209-76-0 cjwatson      OAuth*Token support for macaroons, caveats, and nullable consumer keys

2209-77-0 wgrant        Index LibraryFileAlias expiration queries

2209-77-1 wgrant        GitRepository ID sort indexes and fixed *_default indexes

2209-77-2 wgrant        Uniquely index PackageDiff(from_source, to_source)

2209-77-3 wgrant        Drop disused tables, views and functions

2209-78-0 cjwatson      Drop DistributionSourcePackageCache.changelog from fti

2209-78-1 cjwatson      DistributionSourcePackageCache.archive DROP NOT NULL

2209-78-2 cjwatson      Index DistributionSourcePackageCache(SPN, archive), DistributionSourcePackageCache(binpkgnames)

2209-79-0 maxiberta     Add ArchiveAuthToken.name; make ArchiveAuthToken.person nullable

2209-80-0 cjwatson      Git-targeted code imports

2209-80-1 cjwatson      Per-target-type CodeImport(url) indexes

2209-80-2 cjwatson      GitRepositoryType.repository_type NOT NULL

2209-81-0 wgrant        Function compatibility for PostgreSQL 9.5 and 9.6

2209-82-0 cjwatson      SourcePackageRelease.buildinfo and BinaryPackageBuild.buildinfo

2209-82-1 cjwatson      SourcePackageRelease.buildinfo and BinaryPackageBuild.buildinfo indexes

2209-83-0 cjwatson      Snap.allow_internet

2209-83-1 cjwatson      Snap.auto_build_channels, SnapBuild.channels

2209-83-2 cjwatson      Snap.build_source_tarball

2209-83-3 cjwatson      SnapJob

2209-83-4 cjwatson      SnapBuild.build_request

2209-83-5 twom          SnapBuild.store_upload_json_data

2209-84-0 wgrant        tsearch2 removal

2209-84-1 wgrant        PostgreSQL 10 compatibility

2209-85-0 cjwatson      GitRule, GitGrant

2209-85-1 cjwatson      GitActivity

2209-86-0 cjwatson      PocketChroot.image_type

2209-87-0 abrody        DistributionMirror.https_base_url