This looks great. Comments, naming standards, indexes - all pretty good.
+CREATE TABLE BinaryPackageBuild (
+ id serial PRIMARY KEY,
+ package_build integer NOT NULL CONSTRAINT binarypackagebuild__package_build__fk REFERENCES packagebuild,
+ distro_arch_series integer NOT NULL CONSTRAINT binarypackagebuild__distro_arch_series__fk REFERENCES distroarchseries,
+ source_package_release integer NOT NULL CONSTRAINT binarypackagebuild__source_package_release__fk REFERENCES sourcepackagerelease
I believe Sourcepackage is one word in Launchpad's vocabulary, so that last column should be sourcepackage_release. Confirm with Bjorn.
+CREATE TABLE BinaryPackageBuild (
+ id serial PRIMARY KEY,
+ package_build integer NOT NULL CONSTRAINT binarypackagebuild__package_build__fk REFERENCES packagebuild,
+ distro_arch_series integer NOT NULL CONSTRAINT binarypackagebuild__distro_arch_series__fk REFERENCES distroarchseries,
+ source_package_release integer NOT NULL CONSTRAINT binarypackagebuild__source_package_release__fk REFERENCES sourcepackagerelease
+);
+
+CREATE UNIQUE INDEX binarypackagebuild__package_build__idx ON binarypackagebuild(package_build);
+-- Indexes that we can no longer create:
+-- CREATE UNIQUE INDEX binarypackagebuild__distro_arch_series_uniq__idx ON binarypackagebuild(distro_arch_series, source_package_release, archive)
+-- CREATE INDEX binarypackagebuild__distro_arch_series__status__idx ON binarypackagebuild(distro_arch_series, status?)
+-- CREATE INDEX binarypackagebuild__distro_arch_series__date_finished ON binarypackagebuild(distro_arch_series, date_finished)
+CREATE INDEX binarypackagebuild__source_package_release_idx ON binarypackagebuild(source_package_release);
We might need an index on distro_arch_series too
CREATE INDEX binarypackagebuild__distro_arch_series__idx ON BinaryPackageBuild(distro_arch_series);
+-- Step 2
+-- Migrate the current data from the build table to the newly created
+-- relationships.
+CREATE OR REPLACE FUNCTION migrate_build_rows() RETURNS integer
+LANGUAGE plpgsql AS
+$$
+DECLARE
+ build_info RECORD;
+ rows_migrated integer;
+ buildfarmjob_id integer;
+ packagebuild_id integer;
+BEGIN
+ rows_migrated := 0;
+ FOR build_info IN
+ SELECT
+ build.id,
+ build.processor,
+ archive.require_virtualized AS virtualized,
+ -- Currently we do not know if a build was virtual or not? (it's
+ -- only on the archive and the builder, both of which can
+ -- change). IBuild.is_virtualized just queries the archive.
+ build.datecreated AS date_created,
+ (build.datebuilt - build.buildduration) AS date_started,
+ build.datebuilt AS date_finished,
+ build.date_first_dispatched,
+ build.builder,
+ build.buildstate AS status,
+ build.buildlog AS log,
+ build.archive,
+ build.pocket,
+ build.upload_log,
+ build.dependencies,
+ build.distroarchseries AS distro_arch_series,
+ build.sourcepackagerelease AS source_package_release,
+ build.build_warnings -- We don't seem to use this in LP code at all?
+ FROM
+ build JOIN archive ON build.archive = archive.id
We have to have 'ORDER BY Build.id' at the end here. Any data migration in a DB patch is run individually on each database replica. We have to guarantee that it will run identically everywhere, or replication breaks horribly.
I think we could have done the migration using standard SQL and a temporary table, but the stored procedure is fine too. We can revisit if data migration goes too slowly.
Approved as patch-2207-57-0.sql. The critical fix is the ORDER BY in the stored procedure.
This looks great. Comments, naming standards, indexes - all pretty good.
+CREATE TABLE BinaryPackageBuild ( ild__package_ build__ fk REFERENCES packagebuild, ild__distro_ arch_series_ _fk REFERENCES distroarchseries, package_ release integer NOT NULL CONSTRAINT binarypackagebu ild__source_ package_ release_ _fk REFERENCES sourcepackagere lease
+ id serial PRIMARY KEY,
+ package_build integer NOT NULL CONSTRAINT binarypackagebu
+ distro_arch_series integer NOT NULL CONSTRAINT binarypackagebu
+ source_
I believe Sourcepackage is one word in Launchpad's vocabulary, so that last column should be sourcepackage_ release. Confirm with Bjorn.
+CREATE TABLE BinaryPackageBuild ( ild__package_ build__ fk REFERENCES packagebuild, ild__distro_ arch_series_ _fk REFERENCES distroarchseries, package_ release integer NOT NULL CONSTRAINT binarypackagebu ild__source_ package_ release_ _fk REFERENCES sourcepackagere lease ild__package_ build__ idx ON binarypackagebu ild(package_ build); ild__distro_ arch_series_ uniq__idx ON binarypackagebu ild(distro_ arch_series, source_ package_ release, archive) ild__distro_ arch_series_ _status_ _idx ON binarypackagebu ild(distro_ arch_series, status?) ild__distro_ arch_series_ _date_finished ON binarypackagebu ild(distro_ arch_series, date_finished) ild__source_ package_ release_ idx ON binarypackagebu ild(source_ package_ release) ;
+ id serial PRIMARY KEY,
+ package_build integer NOT NULL CONSTRAINT binarypackagebu
+ distro_arch_series integer NOT NULL CONSTRAINT binarypackagebu
+ source_
+);
+
+CREATE UNIQUE INDEX binarypackagebu
+-- Indexes that we can no longer create:
+-- CREATE UNIQUE INDEX binarypackagebu
+-- CREATE INDEX binarypackagebu
+-- CREATE INDEX binarypackagebu
+CREATE INDEX binarypackagebu
We might need an index on distro_arch_series too
CREATE INDEX binarypackagebu ild__distro_ arch_series_ _idx ON BinaryPackageBu ild(distro_ arch_series) ;
+-- Step 2 build_rows( ) RETURNS integer require_ virtualized AS virtualized, is_virtualized just queries the archive. tion) AS date_started, first_dispatche d, hseries AS distro_arch_series, kagerelease AS source_ package_ release, warnings -- We don't seem to use this in LP code at all?
+-- Migrate the current data from the build table to the newly created
+-- relationships.
+CREATE OR REPLACE FUNCTION migrate_
+LANGUAGE plpgsql AS
+$$
+DECLARE
+ build_info RECORD;
+ rows_migrated integer;
+ buildfarmjob_id integer;
+ packagebuild_id integer;
+BEGIN
+ rows_migrated := 0;
+ FOR build_info IN
+ SELECT
+ build.id,
+ build.processor,
+ archive.
+ -- Currently we do not know if a build was virtual or not? (it's
+ -- only on the archive and the builder, both of which can
+ -- change). IBuild.
+ build.datecreated AS date_created,
+ (build.datebuilt - build.builddura
+ build.datebuilt AS date_finished,
+ build.date_
+ build.builder,
+ build.buildstate AS status,
+ build.buildlog AS log,
+ build.archive,
+ build.pocket,
+ build.upload_log,
+ build.dependencies,
+ build.distroarc
+ build.sourcepac
+ build.build_
+ FROM
+ build JOIN archive ON build.archive = archive.id
We have to have 'ORDER BY Build.id' at the end here. Any data migration in a DB patch is run individually on each database replica. We have to guarantee that it will run identically everywhere, or replication breaks horribly.
I think we could have done the migration using standard SQL and a temporary table, but the stored procedure is fine too. We can revisit if data migration goes too slowly.
Approved as patch-2207- 57-0.sql. The critical fix is the ORDER BY in the stored procedure.